[postgis-tickets] [PostGIS] #3490: Create perl script to add in the ALTER FUNCTION set path calls

[PostGIS]

#3490: Create perl script to add in the ALTER FUNCTION set path calls
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  blocker  |  Milestone:  PostGIS 2.2.2
Component:  postgis  |    Version:  2.2.x
 Keywords:           |
---------------------+---------------------------
 As discussed in several tickets.

 PostGIS functions that call postgis functions fail when the schema that
 postgis is installed in is not in the search_path.

 The generated script will look something like this -
 https://trac.osgeo.org/postgis/browser/trunk/postgis/postgis_functions_search_path.sql
 for will have all functions

 Such a scenario happens in the following cases:

 1. During database restore - raster constraints rely on functions that
 call other functions, and as a result data does not come back if your data
 is not in the same schema as where postgis is installed

 2. Restore of spatial indexes that use ST_Transform, again if your data is
 not in same schema as postgis.

 3. Materialized views restore - views that use ST_Intersects etc. may not
 come back.  So this is fairly serious. Again if not in same schema as
 postgis

 4. Foreign Tables.  The Foreign tables as I recall are set to ignore
 search_path so a foregin table calling ST_Intersects etc will fail if the
 table is not in the same schema as where PostGIS is installed.

 I'm working on a perl script to set the search_path setting of all
 functions.  I plan to distribute this as part of PostGIS 2.2 (not as part
 of extension install).  With an FAQ in the docs that people suffering from
 this issue need to

 1) Install postgis first
 2) Run this script
 3) then restore their data

 For PostGIS 2.3, I'd like to just include this as part of the CREATE
 EXTENSION install so I don't have to explain this to anybody ever again.

 At least 5 people have complained to me about this already and I have
 suffered from it myself.  Enough is enough.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3490>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.