PostgreSQL extensions packaging

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I promised to have an in-depth look at the archives before to spend
time on my ideas for $subject, but failed to do so. Here are the ideas
(user level design if you will) :)

As a PostgreSQL extension developer (see http://pgfoundry.org/projects/prefix)
I'd like to benefit from a dump and restore facility. We don't offer
any easy way for the DBA to restore a dump which happen to depends on
external modules. This proposal tries to solve this by providing a
notion of package.

A module is currently, as far as I understand it, a .so file installed
into some superuser (postgres) owned filesystem place. I'm unsure if
the related .sql file (needed to expose the module functions) is a
part of the module notion, but I don't think so.

A package is a namespace much comparable to a schema, to be found at
the same level (in a database can live many packages), and allowed to
have any SQL object under it. A package can also host variables, which
visibility are package global: any SQL into the package can refer
directly to package variables.
And a package can host modules dependancies (not the .so code itself).

Let's try with an example of an imaginary package declaration:

create or replace package prefix_range
with (version = 0.3, parameter = value, ...)
as $pkg$
declare
prefix_range_global_var text := 'init value';
prefix_range_syntax_error exception;
module
prefix.so;
begin
create schema prefix;

create or replace function prefix_range_in(cstring) ...;
create or replace function prefix_range_out(prefix_range) ...;
create type prefix_range;
create function ...

create operator ...
create operator class ...

-- private hidden things?
create role prefix nologin;
create schema prefix_private owner to prefix;
create table prefix_private.relname ...;
revoke all privileges on prefix_private to public;

-- private stuff ...
-- create table, create index, etc ...
--
-- need some though as to how to allow this from SQL objects
-- declared into the package *only*
end;
$pkg$;

The parameters in the with clause are visible inside the package body
and allow package installer to tune the installation: we could use
this for tablespace creation needs, e.g., and version at least should
be displayed from \dP associated command (is this one free?).

This package creation SQL command would fail if any contained SQL is
wrong, of course, but also if one of the declared modules were not
registered/known/found by the server.

We would certainly want to add a package scope construct to existing
CREATE commands, in order to be able to add a function to a package
without re-running the entire create package command, but this could
come at a later date:
CREATE FUNCTION ... PACKAGE prefix ...

Given this infrastructure, pg_dump would (have to) be able to dump the
SQL and pg_restore to complain when the module dependancies are not
met, error'ing out a list of modules to install.

Now, what would be really good to have would be this pg_pkg command I
was dreaming about in another -hacker mail:
pg_pkg add-mirror http://packages.postgresql.org/
pg-pkg list [remote | available]
pg_pkg add plproxy prefix citext
pg_pkg install plproxy mydatabase
pg_pkg uninstall [--force] plproxy mydatabase
pg_pkg remove <package> ...
...

First, we could have a packages.postgresql.org infrastructure where to
provide source code packages depending on PostgreSQL major version.
Those packages would be known to have received code review and -core
acceptance, so would be as trustworthy as PostgreSQL itself is.
And ideally, any developer could prepare his own PostgreSQL packaging
facility where to propose his own packages, this time out of -core
acceptance, but still integrated into the extension system.

pg_pkg add <package> ... would fetch a source code archive (last
version available, or maybe given version with pg_pkg add prefix=0.3
if we really want this feature) and compile and install it with PGXS.
So you would need to have installed server development support to
benefit from package distribution...

Then pg_pkg install would install given package into given database,
running its CREATE OR REPLACE PACKAGE sql script, responsible of
package object creation and variable, tables, etc initialisation.

The uninstall command would get rid of the package, only to produce
errors if some object existing in the target database had some
dependancy to the package, the -f would force a DROP PACKAGE pkgname
CASCADE;

The remove would get rid of the installed files (modules and .sql),
only when the package is no more in use in any database of the cluster.

With this command set and pg_restore giving a list of missing modules
for each package of a given dump file, it would become easy to restore
a database containing extensions.
$ pg_restore ...
ERROR: failed to create package prefix, missing module prefix.so
$ pg_pkg add prefix
$ pg_restore ...
$ psql -U myuser mydb && enjoy :)

Of course, in case of a remote pg_restore call, the pg_pkg command
line has to be done locally on the target server. Maybe this is a
problem for share hosting facilities, but I don't see pg_restore going
to compile and install stuff on the filesystem by itself.

Anyone willing to share some comments on this dream?
- --
Dimitri Fontaine

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkiHncQACgkQlBXRlnbh1bnreQCcCYMfln8BqDAcGzs1pTBe9RVa
aN8AnjZ3viA9xbVg4Ka2lS0eIrbOJFpV
=FGYQ
-----END PGP SIGNATURE-----