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

[Stephen Frost]

Regina,

* Regina Obe (lr at pcorp.us) wrote:
> In order to schema qualify postgis calls in our functions, we need to make
> PostGIS non-relocateable.

Making PostGIS non-relocatable (that is, you can't change the schema it
was installed into after it's installed) doesn't bother me too much,
personally.

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

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

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

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:

@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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20160401/baca7106/attachment.sig>