SQL objects UNITs (was: Extension Templates S03E11)

Simon Riggs <simon@2ndQuadrant.com> writes:
> On 17 December 2013 23:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> We aim to have the simplest implementation that meets the stated need
>>> and reasonable extrapolations of that. Text in a catalog table is the
>>> simplest implementation. That is not a reason to reject it, especially
>>> when we aren't suggesting a viable alternative.
>>
>> The first part of this assertion is debatable, and the claim that no
>> viable alternative has been suggested is outright wrong.

With due respect, it's only wrong when you buy into implementing
something new rather than improving extensions.

> Sounds like we have a way forward for this feature then, just not with
> the current patch.
>
> Can someone attempt to summarise the way forward, with any caveats and
> necessary restrictions? It would save further column inches of debate.

Here's my attempt:

# Inline Extension, Extension Templates

The problem with *Inline Extension* is the dump and restore policy. The
contents of an extensions are not be found in a `pg_dump` script, ever.

The problem with the *Extension Templates* is that we store the extension
scripts (plain text blobs) in the catalogs, where we already have the full
SQL objects and tools (such as `pg_dump` and `pg_depends`) to manipulate and
introspect them.

# The new thing™

A set of SQL objects that can be managed wholesale, with a version string
attached to it. Objects are part of `pg_dump` output, the whole set can be
relocatable, and has a version string attached.

Name:
 - not `PACKAGE`, Oracle - not `MODULE`, that's already the name of a .so file - not `SYSTEM`, already something else -
`BUNDLE`- `LIBRARY` - `UNIT` 

I'll pick UNIT here.
Commands:
   CREATE UNIT name [ SCHEMA ... ] [ [ NOT ] RELOCATABLE ] [ REQUIRE ...];
   WITH UNIT name;     <commands>   END UNIT name;      ALTER UNIT name OWNER TO <role>;   ALTER UNIT name ADD <object
definition>;  ALTER UNIT name DROP <object definition>;   ALTER UNIT name SET SCHEMA <new schema>;   ALTER UNIT name
UPDATETO <version string>;   ALTER UNIT name SET [ NOT ] RELOCATABLE;   ALTER UNIT name REQUIRE a, b, c; 
   COMMENT ON UNIT name IS '';
   DROP UNIT name [ CASCADE ];

The `UPDATE TO` command only sets a new version string.

# Implementation details

We need a new `pg_unit` catalog, that looks almost exactly like the
`pg_extension` one, except for the `extconfig` and `extcondition` fields.

We need a way to `recordDependencyOnCurrentUnit()`, so another pair of
static variables `creating_unit` and `CurrentUnitObject`. Each and every
command we do support for creating objects must be made aware of the new
`UNIT` concept, including `CREATE EXTENSION`.

The `pg_dump` dependencies have to be set so that all the objects are
restored independently first, as of today, and only then issue `CREATE
UNIT` and a bunch of `ALTER UNIT ADD` commands, one per object.

# Event Trigger support

Event Triggers are to be provided for all the `UNIT` commands.

# Life with Extensions and Units

PostgreSQL now includes two different ways to package SQL objects, with
about the same feature set. The only difference is the `pg_restore`
behavior: *Extensions* are re-created from external resources, *Units* are
re-created from what's in the dump.

The smarts about `ALTER EXTENSION ... UPDATE` are not available when dealing
with *UNITS*, leaving the user or the client scripts to care about that
entirely on their own.

In principle, a client can prepare a SQL script from a PGXN distribution and
apply it surrounded by `WITH UNIT` and `END UNIT` commands.

Upgrade scripts, once identified, can be run as straight SQL, adding a
simple `ALTER UNIT ... UPDATE TO ...` command before the `COMMIT` at the
end of the script. Identifying the upgrade script(s) may require
implementing current Extension update smarts into whatever client side
program is going to be built to support installing from PGXN etc.

# Conclusion

The main advantage of the `UNIT` proposal is that it copes very well with
relations and other usual schema objects, as the data are preserved at
`pg_restore` time.

A `UNIT` can also entirely replace an `EXTENSION`, including when it needs a
*module*, provided that the *module* is made available on the server's file
system before creating the functions in `LANGUAGE C` that depend on it.

It is possible to write a *UNIT distribution network* where a client
software drives the installation of SQL objects within an UNIT, and this
client software needs to include UNIT update smarts too. It's possible also
to build that software as a set of Event Triggers on the `CREATE UNIT` and
`ALTER UNIT UPDATE TO` commands.

# Analysis

The main drawback is that rather than building on extensions, both in a
technical way and in building user trust, we are basically going to
deprecate extensions entirely, giving them a new name an an incompatible way
to manage them.

Only *contribs* are going to be shipped as extensions, as they are basically
the only known extensions following the same delivery rules as the
PostgreSQL core product itself. Almost any other extension existing today
builds support for all the PostgreSQL releases in each version of it,
meaning that the pecularities of `pg_dump` and `pg_restore` are not going to
apply to a `UNIT` in the same way at all.

Basically with building `UNIT` we realise with hindsight that we failed to
build a proper `EXTENSION` system, and we send that message to our users.


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