Re: Extension Templates S03E11 - Mailing list pgsql-hackers

* Dimitri Fontaine (dimitri@2ndQuadrant.fr) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Without that, all of the information about a given extension is already in
> > the database in our dependency system. As you pointed out, there was
>
> That's not entirely true. We would still be missing some information
> from the extension control file.

Fine, so we need an extra side-table, which needn't even be a catalog
table; though, as I've mentioned before, having PG help with managing
these extensions by tracking such information seems reasonable.  What
isn't reasonable is having two nearly complete copies of every extension
installed into a given database.

> > previously a notion of "inline" templates. I'm not sure that particular
> > patch is exactly where we want to go, but I absolutely do not like this
> > idea that we have a "template" on a per-database level which does nothing
> > but duplicate most of the information we *already have*, since you have to
> > assume that if the extension template (which is per-database) exists then
> > the extension has also been created in the database.
>
> That's a classic bootstrap problem. If you consider that the extension
> is already installed, then you don't need to know how to install it.

I don't see how it's a bootstrap problem at all- we just need a way to
install the extension which doesn't involve the filesystem.  That's
completely possible to do *without* storing two complete copies of the
extension in the PG catalogs.

> The main feature that the patch provides is installation path for an
> extension that doesn't involve the server's file system.

No, that isn't what this patch does, which is why I'm so frustrated by
it because what you describe is *exactly what I want*.  This patch
doesn't do that though- it tries to provide a way to *mimic* the
filesystem part of PG extensions through the catalog- on a per-database
level.  There's a number of problems with that approach: the filesystem
*sucks* when it comes to storing the information about an extension- we
do it for extensions today because we're working with the OS packaging
systems; we have *way* better information and understanding about an
extension once it's actually installed into the PG catalogs, why ignore
that instead of use it?; we're duplicating the filesystem-style (which
is next to useless to us..) definition of an extension into every
database we want to install it into for no good purpose; by having it
mimic the filesystem-style approach, it makes it very difficult to
reason about security, if non-superusers should be allowed to install
these things (or the extensions themselves...), etc.

> > Having a versioning notion (and whatever other meta data we, or an
> > extension author, feels is useful) for what are otherwise simple containers
> > (aka the schematic we already have..) makes sense and it would be great to
> > provide support around that, but not this duplication of
> > object definitions.
>
> I don't like duplication either, we've just been failing to find any
> alternative with pg_restore support for the last 3 years.

*That doesn't make this approach the right one*.  If anything, I'm
afraid we've ended up building ourselves a rube goldberg machine because
of this constant struggle to fit a square peg into a round hole.

> If you want the simplest possible patch that would enable you bypassing
> the file system, here's what I would be proposing: have a special flag
> allowing CREATE EXTENSION to just prepare pg_extension catalog entries.
>
> Then create your objects as usual, and use ALTER EXTENSION … ADD … to
> register them against the existing extension.

That's basically what we already do with schemas today and hence is
pretty darn close to what I'm proposing.  Perhaps it'd be a way to
simply version schemas themselves- heck, with that, we could even
provide that oft-asked-for schema delta tool in-core by being able to
deduce the differences between schema at version X and schema at
version Y.

> That would work beautifully, and of course you would have to do that
> again manually at pg_restore time after CREATE DATABASE and before
> pg_restore, or you would need to change the fact that extensions objects
> are not part of your pg_dump scripts, or you would have to name your new
> thing something else than an extension.

We would need a way to dump and restore this, of course.

> Also, please note that I did propose that design when working on the
> first patch series for extension (8.4 and 9.0 eras), or at least some
> variant where the control properties came in from some command rather
> than from a file, and it was rejected because the CREATE EXTENSION
> bootstrapping was then judged too complex, and it was not clear how
> extension authors were going to maintain their scripts.

This just makes me feel like the problem was trying to shoehorn this new
concept into the existing extension system.  I don't see how this would
really change things for extension authors having to maintain their
scripts one way or the other.

> The current extension model is simple enough to reason about. A script
> must be provided in a template and is executed at CREATE EXTENSION time
> or at ALTER EXTENSION UPDATE time, and pg_dump only contains the CREATE
> EXTENSION command, so that pg_restore has to find the template again.

The current extension system is simple.  This addition of extension
templates muddies things *significantly*.
Thanks,
    Stephen