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?