PostgreSQL extensions packaging - Mailing list pgsql-hackers

-----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
restorefacility. 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_vartext := 'init value';   prefix_range_syntax_error exception; module  prefix.so; begin   create
schemaprefix;
 
   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
tableprefix_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
fromSQL 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
mydatabasepg_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-----