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

[Stephen Woodbridge]

On 4/1/2016 9:11 AM, Stephen Frost wrote:
> 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?

What happens if the user does something like:

alter extension set schema to blah;

on a dependent schema? Can you even do that?

-Steve

> 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
>
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-devel
>


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus