From: "Regina Obe" <lr@pcorp.us>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-hackers@postgresql.org>
References: <003001d8f4ae$402282c0$c0678840$@pcorp.us>
 <79300.1668052170@sss.pgh.pa.us> <004501d8f4bf$1213fba0$363bf2e0$@pcorp.us>
 <175298.1668101858@sss.pgh.pa.us>
In-Reply-To: <175298.1668101858@sss.pgh.pa.us>
Subject: RE: Ability to reference other extensions by schema in extension
 scripts
Date: Thu, 10 Nov 2022 13:42:28 -0500
Message-ID: <000001d8f534$2fd7dc90$8f8795b0$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQFgXZxpr0yOB+9uS1Xwc1lFBKthIAHDgsBQATMNnPQCHx2Jfa8A9rVQ
Content-Language: en-us
Archived-At: 
 <https://www.postgresql.org/message-id/000001d8f534%242fd7dc90%248f8795b0%24%40pcorp.us>
Precedence: bulk

> "Regina Obe" <lr@pcorp.us> writes:
> >> 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/20160425232251.GR10850@tamriel.s
> > nowman.net
> > Is that the one you remember?
> 
> 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

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