Re: extensions are hitting the ceiling

Hi all!

I am sending our comments to mentioned issues. I was trying to send it
month ago
(https://www.postgresql.org/message-id/CA%2B8wVNUOt2Bh4x7YQEVoq5BfP%3DjM-F6cDYKxJiTODG_VCGhUVQ%40mail.gmail.com),
but it somehow doesn't append in the "thread" (sorry, I am new in
mailing list practice...).

My colleague already posted some report to bug mailing list
(https://www.postgresql.org/message-id/15616-260dc9cb3bec7e7e@postgresql.org)
but with no response.

On Tue, 19 Mar 2019 at 02:38, Eric Hanson <eric@aquameta.com> wrote:
>
> Hi folks,
>
> After months and years of really trying to make EXTENSIONs meet the requirements of my machinations, I have come to
theconclusion that either a) I am missing something or b) they are architecturally flawed.  Or possibly both. 
>
> Admittedly, I might be trying to push extensions beyond what the great elephant in the sky ever intended. The general
benthere is to try to achieve a level of modular reusable components similar to those in "traditional" programming
environmentslike pip, gem, npm, cpan, etc. Personally, I am trying to migrate as much of my dev stack as possible away
fromthe filesystem and into the database. Files, especially code, configuration, templates, permissions, manifests and
otherdevelopment files, would be much happier in a database where they have constraints and an information model and
canbe queried! 
>
> Regardless, it would be really great to be able to install an extension, and have it cascade down to multiple other
extensions,which in turn cascade down to more, and have everything just work. Clearly, this was considered in the
extensionarchitecture, but I'm running into some problems making it a reality.  So here they are. 
>
>
> #1: Dependencies
>
> Let's say we have two extensions, A and B, both of which depend on a third extension C, let's just say C is hstore.
Aand B are written by different developers, and both contain in their .control file the line 
>
>         requires = 'hstore'
>
> When A is installed, if A creates a schema, it puts hstore in that schema. If not, hstore is already installed, it
usesit in that location.  How does the extension know where to reference hstore? 
>
> Then, when B is installed, it checks to see if extension hstore is installed, sees that it is, and moves on.  What if
itexpects it in a different place than A does? The hstore extension can only be installed once, in a single schema, but
ifmultiple extensions depend on it and look for it in different places, they are incompatible. 
>
> I have heard talk of a way to write extensions so that they dynamically reference the schema of their dependencies,
butsure 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. 
>
> Also it is possible in theory to dynamically set search_path to contain every schema of every dependency in play and
thenjust not specify a schema when you use something in a dependency.  But this ANDs together all the scopes of all the
dependenciesof an extension, introducing potential for collisions, and is generally kind of clunky. 
>

It is not possible to specify the version of extension we are
dependent on in .control file.

> #2:  Data in Extensions
>
> Extensions that are just a collection of functions and types seem to be the norm.  Extensions can contain what the
docscall "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
extensiondoesn't "own", but has as a dependency.  For example, a "webserver" extension might contain a "resource" table
thatserves up the content of resources in the table at a specified path. But then, another extension, say an app, might
wantto just list the webserver extension as a dependency, and insert a few resource rows into it.  This is really from
whatI can tell beyond the scope of what extensions are capable of. 
>

I am not sure about the name "Configuration" Tables. From my point of
view extensions can hold two sorts of data:
1) "static" data: delivered with extension, inserted by update
scripts; the same "static" data are present across multiple
installation of extension in the same version. This data are not
supposed to be dumped.
2) "dynamic" data: inserted by users, have to be included in dumps,
are marked with pg_extension_config_dump and are called
"configuration" tables/data ... but why "configuration"?

>
> #3 pg_dump and Extensions
>
> Tables created by extensions are skipped by pg_dump unless they are flagged at create time with:
>
>         pg_catalog.pg_extension_config_dump('my_table', 'where id < 20')
>
> However, there's no way that I can tell to mix and match rows and tables across multiple extensions, so pg_dump can't
keeptrack of multiple extensions that contain rows in the same table. 
>

We have described some behavior of pg_dump, which we believe are in
fact bugs: see [1] "1) pg_dump with --schema parameter" and "2)
Hanging OID in extconfig".
Maybe it would be good to introduce new switch pg_dump --extension
extA dumping all "dynamic" data from extension tables regardless on
schema

>
> I'd like an extension framework that can contain data as first class citizens, and can gracefully handle a dependency
chainand share dependencies.  I have some ideas for a better approach, but they are pretty radical.  I thought I would
sendthis out and see what folks think. 
>
> Thanks,
> Eric
> --
> http://aquameta.org/

#4: Extension owned

It is not possible to alter extension owner

Thanks for consideration, Jiří & Ivo.

[1] https://www.postgresql.org/message-id/15616-260dc9cb3bec7e7e@postgresql.org