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

Paragon Corporation lr at pcorp.us
Fri Mar 13 21:53:42 PDT 2015

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:



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

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

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


More information about the postgis-devel mailing list