[postgis-devel] PSC Vote: set schema search_path for select functions

[Mark Cave-Ayland]

On 14/03/15 04:53, Paragon Corporation wrote:

> I'm thinking of doing something somewhat controversial which I should
> probably get PSC vote on before I go thru the effort of putting it in and
> testing out.
> 
> We've been getting a lot of complaints of this sort:
> 
> http://trac.osgeo.org/postgis/ticket/3076
> 
> http://trac.osgeo.org/postgis/ticket/2485
> 
> 
> With the rise of materialized views, postgres_fdw,  PostGIS raster,  general
> database users (with more purist ways of organizing data starting to use
> PostGIS)  we are going to hear a lot more screams of this sort coming down
> the pike of the worst case being people not being able to restore their data
> without some workarounds.  I in fact helped a guy just last month and felt
> so bad I didn't charge him much for the consultation.  He was having
> problems restoring data from his production to his identical dev setup and
> spent hours scratching his head.
> 
> 
> So anyway explaining the issue.  This issue arises whenever we have
> functions in PostGIS that call other PostGIS functions or tables and people
> either put their data in a schema other than where they installed PostGIS or
> in case of postgres_fdw where schema is not as controllable.
> 
> This is the case with a lot of the raster constraint functions, many of our
> ST_Distance functions, and ST_Transform to name the main ones that bite
> people.
> 
> My proposition is to in these functions, set the function schema search_path
> to include the common schemas people decide to install PostGIS in.
> So for example for :
> 
> ALTER FUNCTION st_distance(text, text) SET
> search_path=postgis,contrib,extensions,public;
> 
> I would put this in a separate file to be included just so it doesn't mess
> up our postgis parsers and can be easily pulled out if we find the need to.
> 
> 
> This is a bit of a breaking change in that if people don't install PostGIS
> in one of the common schemas we assume they would, then functions will just
> not work for them since the Function search_path will override whatever
> search path they set.
> 
> The above seems like a less bothersome issue than people not being able to
> restore their data without having a doctorate in PostGIS.
> 
> Does anyone have an issue with this change?  I was going to put it in
> PostGIS 2.2 and then backport it to 2.1 after doing some more extensive
> tests.

Hmmmm I'm not convinced this is the right thing to do here. There are
some notes in section 35.15 of the PostgreSQL documentation relating to
extensions which cover relocation, but not for this exact case. I'd say
it's probably worth starting a thread on pgsql-hackers to explore the
options available before committing to one particular approach, and even
then it may be that some historical users will still need to make manual
changes.


ATB,

Mark.