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

[Regina Obe]

>> 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.

>> That's an interesting issue, certainly..  That said, if it's C code,
couldn't you simply look up the schema that the extension is installed into
and build that into the query you're sending through SPI (apologies if I'm
not fully understanding the issue here).
That would work.  Only  issue with that is not everyone installs with
extensions, so it might not show up.  I guess for those cases we just assume
it's in search path as we do now and screw you if it isn't and you aren't
using postgis extension approach :)
 

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

> Yeah, I tend to share that frowny face, to be honest. :)
Okay I admit I frown too so I'm pushing that way down the priority as it may
cause more problems than it solves.
I think making postgis not relocateable and us schema qualifying our calls
would deal with 95% of the issue.  The other 5% I fear will grow as more and
more extensions rely
On us. 
IN case of postgis_sfcgal we can deal with that by schema qualifying all
those and since that is part of postgis, people just need to understand they
have to install both in the same schema.
Postgis_topology luckily doesn't have any functions YET that are used for
index building etc that use PostGIS where restore would become an issue.
Still it could happen and it's still possible
For some crazy person to use postgis_topology in a materialized view.
That's more of a theoretical problem at moment than a real problem.

> Ok, I understand that the issue here is that there's no way inside of the
script run at CREATE EXTENSION time to refer to the schema of
> *another* extension.  You can refer to the extension that your extension
is being installed into using '@extschema@', but that doesn't help you if
you depend on objects from another extension.

> That's a pretty rough lack-of-capability on the PG side.  Looking at the
code which handles '@extschema@', it might not be too hard to add in
something along the lines of:

 It's a mess in postgis_tiger_geocoder because of reliance of fuzzystrmatch.
As you said this is really a PostgreSQL issue
That PostgreSQL group is going to have to deal with sooner or later as more
extensions rely on each other.  So I'd rather us not have to deal with it.


> @extschema{'postgis'}@

> and have that be replaced with the location of the postgis extension.
> My thinking here for how the code would work is that we'd simple run that
'replace_text' function for all extensions which are in the 'requires' list
of the control file (substituting the name of each extension, of course).

> Thoughts?

> Note that we've only got a week before feature free for 9.6, so we really
need to think about this hard but also quickly if we're going to get such a
change in.  If it sounds like it might work to you then we should bring it
to the -hackers list ASAP.

> Thanks!

> Stephen

On surface sounds fine to me.  I wasn't expecting a fix in 9.6 for this
though would be nice if there was one.  Given we have to back support a
couple of versions of PostgreSQL (and so does pgRouting where I fear this
will become more of a problem)
We wouldn't be able to use this for a while anyway.

Also since most of these issues are theoretical issues at this time, I'm not
too worried about waiting.  But like I said as more extensions are added to
PostgreSQL and more rely on each other,
this will become more of a serious issue that PostgreSQL dev is going to be
forced to deal with. So you should get prepared.  This is just a
foreshadowing warning.

Thanks,
Regina