Re: extensions are hitting the ceiling

On Tue, Apr 16, 2019 at 12:47 AM Noah Misch <noah(at)leadboat(dot)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...

> > #2: Data in Extensions
> >
> > Extensions that are just a collection of functions and types seem to be
> the
> > norm. Extensions can contain what the docs call "configuration" data,
> but
> > rows are really second class citizens: They aren't tracked with
> > pg_catalog.pg_depend, they aren't deleted when the extension is dropped,
> > etc.
> >
> > Sometimes it would make sense for an extension to contain *only* data, or
> > insert some rows in a table that the extension doesn't "own", but has as
> a
> > dependency. For example, a "webserver" extension might contain a
> > "resource" table that serves up the content of resources in the table at
> a
> > specified path. But then, another extension, say an app, might want to
> just
> > list the webserver extension as a dependency, and insert a few resource
> > rows into it. This is really from what I can tell beyond the scope of
> what
> > extensions are capable of.
>
> I never thought of this use case. Interesting.
>

It's a *really* powerful pattern. I am sure of this because I've been
exploring it while developing a row packaging system modeled after git [1],
and using it in conjunction with EXTENSIONs with extreme joy. But one does
rows, and the other does DDL, and this is not ideal.

Cheers,
Eric

[1]
https://github.com/aquametalabs/aquameta/tree/master/src/pg-extension/bundle