[postgis-devel] Making PostGIS not schema relocateable to fix materialized view, restore issues, foreign table issues

[Regina Obe]

I've been giving this a lot of thought and have my proposition in PostGIS
2.3

https://trac.osgeo.org/postgis/ticket/3496

I want to know if people have issue with this before I push forward.


Right now for PostGIS 2.2 I had included a conditional script that adds
search_path to many functions to fix the infamous raster restore issues and
some issues people were having with materialized views.

In PostGIS 2.3, I made this script included as part of extension install.
Eventually I will probably take some of this out and go more with
selectively schema qualifying function calls.

In order to schema qualify postgis calls in our functions, we need to make
PostGIS non-relocateable.

The reason why doing the search_path thing was not good enough is 

1) In certain situations it changes the planner's behavior. E.g. it prevents
inlining from happening which meant I couldn't put in in functions like 
ST_DWithin, ST_Intersects that are commonly used -
http://www.postgresql.org/message-id/9914.1457628521@sss.pgh.pa.us


2) There was also some weird slowness in putting it in functions used in
casting, so limited it to just plpgsql and STRICT sql functions.

3) It however would still be needed on some C functions such as
ST_Transform.  That one I hard-coded.  Reason for that is any C function
that makes calls to spatial_ref_sys  or any other postgis function
Either needs a search_path or schema qualify
And that is later used e.g. in a materialized view etc, would suffer the
same load issues.  Since we can't have our C code be changed at runtime, the
only way to fix it is to put it in the 

CREATE FUNCTION  ... ALTER search_path

I think we may have added some other transform like thingys we may need to
do this for.

--- NOW for the more invasive plan, which Paul already gave his frowny face
about.

WE NEED TO AGREE ON A SCHEMA THAT postgis lives in and first encourage and
then force everyone to install there.  
This is admittedly a big breaking change so should wait for PostGIS 2.4 (or
see if PostgreSQL upstream comes up with a better solution). 
I have complained to them extensively about this issue (which I'll get to in
a minute).

Here is why I feel we need to move in that direction.

We have some extensions that rely on PostGIS already -- postgis_topology,
pgRouting, postgis_tiger_geocoder, postgis_sfcgal.

If anybody uses any of the functions in these extensions in a materialized
view or table index or constraint that happens to call a PostGIS function
they are screwed.
They are screwed because we are preventing these extensions from schema
qualifying their PostGIS calls and they can't schema qualify them if there
is no agreed location where PostGIS is installed.


I'm hoping PostgreSQL dev group  will do something about this 
http://www.postgresql.org/message-id/56E36445.3050303@BlueTreble.com  so we
don't have to.

because it's not just us, but given our extension size, we are probably most
affected and I fear as use of materialized views, foreign tables, more
postgis related extensions etc grows, this is going to be more of a problem

We have various tickets about this - raster restore (which we can consider
already fixed), materialized views, foreign tables (Haven't figured how this
happens I suspect it affects foreign tables if the foreign table is a link
to a view that has PostGIS functions, 
And it has a function that calls another postgis function - foreign table
schema path is isolated evidentally so will not use the default search_path)


Thanks,
Regina