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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
