in-catalog Extension Scripts and Control parameters (templates?)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> CREATE TEMPLATE yadda;
>> ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$;
>
> FWIW, the more I think about it the more I like the notion of treating
> "extension templates" as a separate kind of object. I do see value in
> storing them inside the database system: transactional safety, the
> ability to identify an owner, etc etc. But conflating this
> functionality with installed extensions is just going to create
> headaches.

I totally agree that the current proposal is somewhat of a mess, and
making a distinction between an extension and its packaging seems like a
good approach to the problem.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> A dump-level option for that seems completely wrong in any case: it
> breaks one of the fundamental design objectives for extensions, or
> at least for extensions as originally conceived. It might be necessary
> to do it this way for these new critters, but that just reinforces the
> point that you're designing a new kind of object.

Well what this template idea is saying to me is that once installed,
we're still talking about an extension, the exact same thing.

> I think a separate kind of "extension template" object would make a lot
> more sense.

I'm on board now. We still have some questions to answer, and here's a
worked out design proposal for implementing my understanding of your
"extension's template" idea:

- Extension Scripts are now stored in the catalogs, right?

problem: pg_extension_script(extension, version, fromversion, script)
what's the unique key when fromversion is nullable?

so I would propose to have instead:

pg_extension_install_script(extension, version, script)
unique(extension, version)

pg_extension_update_script(extension, oldversion, newversion, script)
unique(extension, oldversion, newversion)

- The control file should get in the catalogs too, and as it can get
some per-version changes, it needs to be stored separately:

pg_extension_control(extension, version, default_version,
default_full_version, module_pathname,
relocatable, superuser, schema, requires)
unique(extension, version)

We would do the secondary control file overriding at creation time.

- The naming "TEMPLATE" appears to me to be too much of a generic
naming for our usage here, so I'm not sure about it yet. On the other
hand the following proposal would certainly require to reserve new
keywords, which we want to avoid:

CREATE EXTENSION PARAMETERS FOR 'version' [ WITH ] key = val…
CREATE EXTENSION SCRIPT FOR 'version' AS $$ … $$;
CREATE EXTENSION SCRIPT FROM 'version' TO 'version' AS …

So maybe what we could do instead is something like the following:

ALTER EXTENSION … CONFIGURATION FOR 'version' SET param = value, …;
ALTER EXTENSION … SET SCRIPT FOR 'version' AS $$ … $$;
ALTER EXTENSION … SET SCRIPT FROM 'version' TO 'version' AS …

Oh actually TEMPLATE is already a keyword thanks to text search, so
another alternative would be the following, if we really really want
to avoid any new keyword in our grammar:

ALTER EXTENSION … CONFIGURATION FOR 'version' SET param = value, …;
ALTER EXTENSION … SET TEMPLATE FOR 'version' AS $$ … $$;
ALTER EXTENSION … SET TEMPLATE FROM 'version' TO 'version' AS …

That would mean that ALTER EXTENSION could create objects in other
catalogs for an extension that does not exists itself yet, but is now
known available (select * from pg_available_extensions()).

We already have commands that will create subsidiary objects in other
places in the catalogs (serial, composite types, array types) but all
of those are using the new object in the command itself. So that
would be new, but it allows for not having any new keyword here.

The $2.56 question being what would be the pg_dump policy of the
"extension templates" objects? I suppose the whole game here is to dump
them all by default, which would just work at pg_restore time too.

It's possible to filter templates out at dump or restore time if you
need to install a new set of templates for a given extension before to
run CREATE EXTENSION so that's ok.

Now, my understanding is that CREATE EXTENSION would check for templates
being already available in the catalogs and failing to find them would
have to do the extra steps of creating them from disk files as a
preparatory step, right? (backward compatibility requirement)

Finally, while we're talking about reflecting on-disk objects into the
catalogs, do we want to have a pg_module catalog where we list all
shared objects binaries we know about, with a boolean column to indicate
which of those we loaded in the current session, and by which extension
if any?

I don't think we could easily match a .so with an extension's template
so I won't be proposing that, but we could quite easily match them now
with extensions, because we're going to have to LOAD the module while
creating_extension = true.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support