Re: extensions are hitting the ceiling

On Tue, Apr 16, 2019 at 12:47 AM Noah Misch <noah@leadboat.com> wrote:

On Mon, Mar 18, 2019 at 09:38:19PM -0500, Eric Hanson wrote:
> I have heard talk of a way to write extensions so that they dynamically
> reference the schema of their dependencies, but sure don't know how that
> would work if it's possible.  The @extschema@ variable references the
> *current* extension's schema, but not there is no dynamic variable to
> reference the schema of a dependency.

If desperate, you can do it like this:

  DO $$ BEGIN EXECUTE format('SELECT %I.earth()',
    (SELECT nspname FROM pg_namespace n
     JOIN pg_extension ON n.oid = extnamespace
     WHERE extname = 'earthdistance' )); END $$;

Needless to say, that's too ugly.  Though probably unimportant in practice, it
also has a race condition vs. ALTER EXTENSION SET SCHEMA.

> Also it is possible in theory to dynamically set search_path to contain
> every schema of every dependency in play and then just not specify a schema
> when you use something in a dependency.  But this ANDs together all the
> scopes of all the dependencies of an extension, introducing potential for
> collisions, and is generally kind of clunky.

That's how it works today, and it has the problems you describe.  I discussed
some solution candidates here:
https://www.postgresql.org/message-id/20180710014308.GA805781@rfd.leadboat.com

The @DEPNAME_schema@ thing was trivial to implement, but I shelved it.  I'm
attaching the proof of concept, for your information.

Interesting.

Why shelved?  I like it.  You said you lean toward 2b in the link above, but there is no 2b :-) but 1b was this option, which maybe you meant?

The other approach would be to have each extension be in it's own schema, whose name is fixed for life.  Then there are no collisions and no ambiguity about their location.   I don't use NPM but was just reading about how they converted their package namespace from a single global namespace with I think it was 30k packages in it, to @organization/packagename.  I don't know how folks would feel about a central namespace registry, I don't love the idea if we can find a way around it, but would settle for it if there's no better solution.  Either that or use a UUID as the schema name.  Truly hideous.  But it seems like your approach above with just dynamically looking up the extension's schema as a variable would solve everything.

There is the problem of sequencing, where extension A installs dependency extension B in it's own schema.  Then extension C also wants to use dependency B, but extension A is uninstalled and extension B is now still hanging around in A's old schema.  Not ideal but at least everything would still function.

I'll keep thinking about it...