Re: SQL objects UNITs (was: Extension Templates S03E11)

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> 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.

You keep coming back to this and I think you're taking too narraw a view
to the comments made on the prior threads. No, we don't really want
extensions which have .sql files out on disk somewhere as part of
them to be dumped out through pg_dump because then it becomes unclear
which set of scripts should be used during restore. What we're talking
about here is intended to not have that issue by using a different
namespace, a flag, something which identifies these extensions as being
defined through the catalog instead.

> # 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.

I'd like to see more than just a single version string included and I
think that'd be beneficial for extensions too.

> Name:
[...]
> I'll pick UNIT here.

We can figure that later.

> Commands:
>
> CREATE UNIT name [ SCHEMA ... ] [ [ NOT ] RELOCATABLE ] [ REQUIRE ...];
>
> WITH UNIT name;
> <commands>
> END UNIT name;

Interesting approach- I had considered something similar by having a
'fake' schema created into which you built up the 'UNIT'. The reason I
was thinking schema instead of begin/end style commands, as you have
above, is because of questions around transactions. Do you think the
syntax you have here would require the definition to be all inside of a
single transaction? Do we feel that would even be an issue or perhaps
that it *should* be done that way? I don't currently have any strong
feelings one way or the other on this and I'm curious what others
think.

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

So, one of the things I had been wondering about is if we could provide
a 'diff' tool. Using your 'WITH UNIT' syntax above, an author might
need to only write their initial implementation, build up a 'UNIT' based
on it, then adjust that implementation with another 'WITH UNIT' clause
and then ask PG for the differences. It's not clear if we could make
that work but there is definitely a set of desireable capabilities out
there, which some other databases have, around automated upgrade script
building and doing schema differences.

> # Implementation details
> # Event Trigger support

Not sure we're really ready to get into these yet.

> 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.

I don't see this as ending up deprecating extensions, even if we build a
new thing with a new name. I would argue that properly supported
extensions, such as those in contrib and the other 'main' ones, like
PostGIS, and others that have any external dependencies (eg: FDWs) would
almost certainly continue as extensions and would be packaged through
the normal OS packaging systems. While you plan to use the event
trigger mechanism to build something on top of this which tries to act
like extenisons-but-not, I think that's an extremely narrow and limited
use-case that very few people would have any interest in or use.

> 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.

Little difficult to draw conclusions about what out 'hindsight' will
look like.

Thanks,

Stephen