Skip site navigation (1) Skip section navigation (2)

PostgreSQL extensions packaging

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: PostgreSQL extensions packaging
Date: 2008-07-23 21:08:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hash: SHA1


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 
  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$
    prefix_range_global_var text := 'init value';
    prefix_range_syntax_error exception;
    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*

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:

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
  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 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  

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
   $ 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

Version: GnuPG v1.4.9 (Darwin)



pgsql-hackers by date

Next:From: Tom LaneDate: 2008-07-23 21:26:49
Subject: Re: [PATCHES] GIN improvements
Previous:From: Andrew DunstanDate: 2008-07-23 21:01:18
Subject: Re: [PATCHES] odd output in restore mode

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group