Subject: Ability to reference other extensions by schema in extension scripts

From: "Regina Obe" <[email-masked]>
Date: Wed, 9 Nov 2022 21:43:43 -0500

I think I had brought this up a while ago, but I forget what the opinion was
on the matter.

PostGIS has a number of extensions that rely on it.  For the extensions that
are packaged with PostGIS, we force them all into the same schema except for
the postgis_topology and postgis_tiger_geocoder extensions which are already
installed in dedicated schemas.

This makes it impossible for postgis_topology and postgis_tiger_geocoder to
schema qualify their use of postgis.  Other extensions like pgRouting,
h3-pg, mobilitydb have similar issues.

My proposal is this.  If you think it's a good enough idea I can work up a
patch for this.

Extensions currently are allowed to specify a requires in the control file.

I propose to use this information, to allow replacement of phrases

@extschema_nameofextension@  as a variable, where nameofextension has to be
one of the extensions listed in the requires.

The extension plumbing will then use this information to look up the schema
that the current required extensions are installed in, and replace the
variables with the schema of where the dependent extension is installed.

Does anyone see any issue with this idea. 

Thanks,
Regina

---

From: Tom Lane <[email-masked]>
Date: Wed, 09 Nov 2022 22:49:30 -0500

> one of the extensions listed in the requires.

I have a distinct sense of deja vu here.  I think this idea, or something
isomorphic to it, was previously discussed with some other syntax details.
I'm too lazy to go searching the archives right now, but I suggest that
you try to find that discussion and see if the discussed syntax seems
better or worse than what you mention.

I think it might've been along the line of @extschema:nameofextension@,
which seems like it might be superior because colon isn't a valid
identifier character so there's less risk of ambiguity.

			regards, tom lane

---

From: "Regina Obe" <[email-masked]>
Date: Wed, 9 Nov 2022 23:44:07 -0500

> > My proposal is this.  If you think it's a good enough idea I can work
> > up a patch for this.
> > @extschema_nameofextension@  as a variable, where nameofextension has
> > to be one of the extensions listed in the requires.
> 
you try to
> find that discussion and see if the discussed syntax seems better or worse
than
> what you mention.
identifier
> character so there's less risk of ambiguity.
> 
> 			regards, tom lane
I found the old discussion I recalled having and Stephen had suggested using

@extschema{'postgis'}@

On this thread --
https://www.postgresql.org/message-id/[email-masked]
.net

Is that the one you remember?

Thanks,
Regina

---

From: Tom Lane <[email-masked]>
Date: Thu, 10 Nov 2022 12:37:38 -0500

> https://www.postgresql.org/message-id/[email-masked]

Hmmm ... no, ISTM it was considerably more recent than that.
[ ...digs... ]  Here we go, it was in the discussion around
converting contrib SQL functions to new-style:

https://www.postgresql.org/message-id/flat/3395418.1618352794%40sss.pgh.pa.us

There are a few different ideas bandied around in there.
Personally I still like the @extschema:extensionname@
option the best, though.

			regards, tom lane

---

From: "Regina Obe" <[email-masked]>
Date: Thu, 10 Nov 2022 13:42:28 -0500

> >> I have a distinct sense of deja vu here.  I think this idea, or
> >> something isomorphic to it, was previously discussed with some other
> syntax details.
> 
> > I found the old discussion I recalled having and Stephen had suggested
> > using @extschema{'postgis'}@ On this thread --
> > https://www.postgresql.org/message-
> id/[email-masked]
> > nowman.net
> [ ...digs... ]  Here we go, it was in the discussion around converting
contrib SQL
> functions to new-style:
> 
> https://www.postgresql.org/message-
> id/flat/3395418.1618352794%40sss.pgh.pa.us
> 
> Personally I still like the @extschema:extensionname@ option the best,
> though.
> 
> 			regards, tom lane

I had initially thought of a syntax that could always be used even outside
of extension install as some mentioned. Like the PG_EXTENSION_SCHEMA(cube)
example. Main benefit I see with that is that even if an extension is moved,
all the dependent extensions that reference it would still work fine.

I had dismissed that because it seemed too invasive.  Seems like it would
require changes to the parser and possibly add query performance overhead to
resolve the schema. Not to mention the added testing required to do no harm.

The other reason I dismissed it is because at least for PostGIS it would be
harder to conditionally replace. The issue with
PG_EXTENSION_SCHEMA(cube) is we can't support that in lower PG versions so
we'd need to strip for lower versions, and that would introduce the
possibility of missing
PG_EXTENSION_SCHEMA(cube) vs. PG_EXTENSION_SCHEMA( cube ), not a huge deal
though, but not quite as easy and precise as just stripping
@extschema:extensionname@. References.

With the @extschema:extensionname@, it doesn't solve all problems, but the
key ones we care about like breakage of functions used in indexes,
materialized views, and added security and is a little easier to strip out.

I'll work on producing a patch.

Thanks,
Regina

---

From: "Regina Obe" <[email-masked]>
Date: Tue, 22 Nov 2022 23:24:19 -0500

contrib SQL
> Personally I still like the @extschema:extensionname@ option the best,

Here is first version of my patch using the @extschema:extensionname@ syntax
you proposed.

This patch includes:
1) Changes to replace references of @extschema:extensionname@ with the
schema of the required extension
2) Documentation for the feature
3) Tests for the feature.

There is one issue I thought about that is not addressed by this.

If an extension is required by another extension and that required extension
schema is referenced in the extension scripts using the
@extschema:extensionname@ syntax, then ideally we should prevent the
required extension from being relocatable.  This would prevent a user from
accidentally moving the required extension, thus breaking the dependent
extensions.

I didn't add that feature cause I wasn't sure if it was overstepping the
bounds of what should be done, or if we leave it up to the user to just know
better.

Thanks,
Regina

---

From: Sandro Santilli <[email-masked]>
Date: Thu, 15 Dec 2022 09:52:34 +0100

> bounds of what should be done, or if we leave it up to the user to just know

An alternative would be to forbid using @extschema:extensionname@ to
reference relocatable extensions. DBA can toggle relocatability of an
extension to allow it to be referenced.

--strk;

---

From: "Regina Obe" <[email-masked]>
Date: Thu, 15 Dec 2022 08:04:22 -0500

> > Here is first version of my patch using the @extschema:extensionname@
> > syntax you proposed.
> >
> > This patch includes:
> > If an extension is required by another extension and that required
> > extension schema is referenced in the extension scripts using the
> > required extension from being relocatable.  This would prevent a user
> > from accidentally moving the required extension, thus breaking the
> > dependent extensions.
> >
> > I didn't add that feature cause I wasn't sure if it was overstepping
> > the bounds of what should be done, or if we leave it up to the user to
> > just know better.
> 
extension
> to allow it to be referenced.
> 
> --strk;
That would be hard to do in a DbaaS setup and not many users know they can
fiddle with extension control files.
Plus those would get overwritten with upgrades.

In my case for example I have postgis_tiger_geocoder that relies on both
postgis and fuzzystrmatch.
I'd rather not have to explain to users how to fiddle with the
fuzzystrmatch.control file to make it not relocatable.

But I don't think anyone would mind if it's forced after install because
it's a rare thing for people to be moving extensions to different schemas
after install.  

Thanks,
Regina