Re: Is there a way around function search_path killing SQL function inlining? - Mailing list pgsql-hackers

> Michael Banck <michael.banck@credativ.de> writes:
>> As I've been bitten by this problem recently, I thought I'd take a 
>> look at editing the PostGIS extension SQL file to this end, but 
>> contrary to the above, the @extschema@ feature only applies to 
>> non-relocatable extensions, from src/backend/commands/extension.c:

>>   * If it's not relocatable, substitute the target schema name for
>>  * occurrences of @extschema@.
>>   *
>>   * For a relocatable extension, we needn't do this.  There cannot be
>>   * any need for @extschema@, else it wouldn't be relocatable.

>> I'm not sure that logic is sound - even if setting @extschema@ 
>> explicitly in the SQL functions bodies kills inlining (not sure about
>> that) or wouldn't help for other reasons, ISTM this should be 
>> reconsidered in the light of the use case with materialized views 
> > during restore.

> It's not simply a matter of allowing the substitution to occur while
reading the extension script.  "Relocatable" means that we support ALTER
EXTENSION SET SCHEMA, which means moving all the 
> extension's objects into some new schema.  There's no good way to run
around and find places where @extschema@ was replaced in order to change
them to something else.

> Basically the point of @extschema@ is to support extensions that are
relocatable at installation time, but not afterwards.

>            regards, tom lane

FWIW on upcoming PostGIS 2.3, we have changed to not allow PostGIS to be
relocatable and schema qualifying internal calls. I took Tom's suggestion of
just using @extschema@
Which did mean we needed to not allow PostGIS to be relocatable anymore.  A
bit of a bummer.

Setting search_path on functions aside from killing inlining also killed
performance in other ways so that was a no go. Not sure if that is a known
issue or not and I haven't determined under what circumstances setting
search_path kills performance when index usage does not come into play.
I'll take it as a known.
Here is an example of such a case. 

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

Now getting to the fact that using @extschema@ means requiring extension not
to be relocatable, that was a bummer and something we would need to deal
with if we ever forced everyone to install PostGIS in a specific schema so
that other extensions that rely on us can just know where PostGIS is
installed (or as Steve Frost suggested a way for dependency extensions to be
able to specify location of dependent extensions with a code such as
@extschema_postgis@ as we've got a bunch of extensions we are aware of
relying on postgis already (pgrouting, postgis_sfcgal, postgis_topology,
postgis_tiger_geocoder)

It would also be nice if the extension model had a way to allow the
extension authors the choice of handling the 'ALTER EXTENSION SET SCHEMA'
event short of monkeying with event triggers.

Yes we really need an extensions authors list to iron out and hear about
these pain points.  :)

Thanks,
Regina