Re: Extensions User Design

On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote:
> === installing and removing an extension
>
> begin;
> install extension foo with search_path = foo;
> commit;
>
> Extensions authors are asked not to bother about search_path in their sql
> scripts so that it's easy for DBAs to decide where to install them. The
> with strange syntax is there to allow for the "install extension" command
> to default to, e.g., pg_extension, which won't typically be the first
> schema in the search_path.
>
> begin;
> drop extension foo [cascade];
> commit;
>
> The "cascade" option is there to care about reverse depends.

I have been thinking about a different use case for this, and I wonder whether
that can fit into your proposal.

Instead of installing an "extension", that is, say, a collection of types and
functions provided by a third-party source, I would like to have a mechanism
to deploy my own actual database application code.

That is, after all, how I work with non-database deployments: I build a
package (deb, rpm) from the code, and install it on the target machine. The
package system here functions as a deployment aid both for "extensions" of the
operating system and for local custom code.

Applying this method to database code, with regard to your proposal, means
first of all that naming this thing "extension" is questionable, and that
installing everything by default into some schema like pg_extensions is
inappropriate.

If you look at how a dpkg or rpm package is structured, it's basically an
archive (ar or cpio) of the files to install plus some control information
such as name, version, dependencies, and various pre/post scripts. We already
have the first part of this: pg_dump/pg_restore are basically tools to create
an archive file out of a database and extract an archive file into a database.
I have been toying with the idea lately to create a thin wrapper around
pg_restore that would contain a bit of metainformation of the kind listed
above. That would actually solve a number of problems already. And then, if
pg_restore could be taught to do upgrades instead of just overwriting (e.g.,
ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all
fall into place nicely.

What this needs below the surface is basically librpm: an interface to
describe and query which objects belong to which "package" and to associate
pre/post scripts with packages. And I think that that interface is quite like
the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts
could be functions, actually, instead of scripts.)

On the matter of schemas, I suggest that we consider two ideas that have
helped RPM in its early days, when everyone had their own very specific ideas
about what should be installed where:

- file system hierarchy standard
- relocations

This means, we'd write up standard of where we think you *should* install
things. And we expect that quality packages/bundles/extensions created for
wider distribution install themselves in the right place without additional
user intervention. But the packaging tool would provide a way to override
this. Then, something that is a true extension could in fact be set up to
install itself by default into pg_extensions, but a bundle containing local
custom code would be set up so that it installs into a different schema or
schemas by default.

What do you think?