Extensions User Design - Mailing list pgsql-hackers

Hi,

= PostgreSQL Extensions

Here's the first round of User Design about PostgreSQL Extensions. I tried
to put together the ideas expressed by a lot of different people. The aim
here is to first agree on the naming and the goals, then talk about what
user design we propose.

== name

The contenders are extension, module, bundle and package. My vote is
extension.

The module is something else in the SQL standard, a bundle is an ok choice,
a package would certainly make people think we're Oracle compatible (and we
don't want to have Ada like skeleton and bodies), and extension is what PGXS
is make for and what we -you-name-it- authors made.

== v1.0 goals

We're not trying to be feature complete on first round.

* must have

 - dump & restore support (when upgrading a cluster or just restoring)

 - easy install and uninstall

 - support for home grown SQL/PLpgSQL only extensions in order to make life
   easier for in-house PG based development (you don't have to code in C to
   benefit from extensions)

 - support for "basic" modules, providing a type and its operators and
   indexing support, such as ip4r, hstore, temporal, prefix and many others,
   you name it, of even simpler things like preprepare or
   backports/min_update.

 - support for procedural languages (a priori easily covered within basic
   modules but I'm not sure) like plproxy, pllolcode, pllua, plscheme, plsh
   et al.

 - support for all what you find in contrib/ for 8.4 (covered already?)

* would be great (target later commit fest)

 - versioning support with upgrade in place facility (hooks?)

 - supporting more than one version of the same module installed in the same
   time, possibly (I suppose always but...) in different schemas

 - custom variables?

 - PostGIS complete support, with user data dependancy, even if an
   extensible typmod system would certainly solve this problem in a better
   place. Maybe someone will come up with another existing extension sharing
   the problem and not the typmod solution?

 - a core team approved list of extensions (replacing contribs, maybe adding
   to it), where approved means code has been reviewed and the only reason
   why it's not in the core itself is that core team feels that it's not
   part of a RDBMS per-se, or feel like the code should be maintained and
   released separately until it gets some more field exposure... (think
   plproxy).

* later please

 - CPAN or ports like infrastructure for auto downloading a more or less
   prepared "bundle", place it at the right place on the filesystem and
   install it in the database(s) of choice

 - complex support for ad-hoc bootstrap of uncommon modules such as pljava

 - dependancy graph solving and automatic installation, with depends,
   recommends and suggest sections and with rules/setup to choose what to
   pull in by default...

== dump & restore

We want pg_dump to issue only one line per extension, the one installing the
extension in the database, see syntax.

== syntax

Extensions will need metadata, and after reading several proposals, what I
propose here is to have a first explicit step to register the extension name
and metadata, then have "basic" tools to play with it.

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

=== creating extensions (authoring)

The 'foo' extension author is meant to provide a +foo.sql+ file containing
this:

  create extension foo
    with version 1.0
         install [script] 'foo.install.sql'
         uninstall [script] 'foo.uninstall.sql'
     upgrade function upgrade_foo(old version, new version)
     [ custom_variable_classes 'a,b'
           configuration file 'foo.conf' ]
    depends on bar version 0.3
        and on baz version >= 1.2;

Here we suppose we have also a new datatype "version" to host the
versionning information, with the associated operators. See
  http://packages.debian.org/sid/postgresql-8.3-debversion

Doing it this way, we skip the need to provide a way of telling "next
comands are meant for creating SQL objects which belongs to such extension",
at the expense of forcing authors to manage upgrades to add objects.

The upgrade function is mandatory, and has to return the installed version
or null, meaning "please run the install script again, that's how I
upgrade". The error management is to be made by means of RAISE EXCEPTION.

If a specific function is to get called at install or uninstall time, it's
easy enough to SELECT install_function(); from within the install script,
after having defined it. To support this, internal GUCs (not exposed in
postgresql.conf) will be provided and set by PG when running those scripts,
named current_extension and current_extension_version.

== ACLs

The "bulk" ACL management of an extension's objects is pushed to the globing
support project for GRANT/REVOKE, so we don't have to speak about what it'll
look like here :)

== OS Filesystem Interaction

PostgreSQL already provides standard paths where to install extensions by
means of PGXS, and distribution packagers have been able to adapt those. We
should just stick with this, meaning the problem is solved.

--
dim

PS: using the asciidoc syntax, which allowed me have a nice HTML
browsable document. Hope you don't mind, dear reader.