Upgrading Extension, version numbers (was: Extensions, patch v16)

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Dec 29, 2010 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We had a long discussion upthread of what version numbers to keep where.
>> IMHO the Makefile is about the *least* useful place to put a version
>> number; the more so if you want more than one.  What we seem to need is
>> a version number in the .sql file itself (so that we can tell whether we
>> need to take action to update the extension's catalog entries).  I'm not
>> convinced yet whether there needs to be another version number embedded
>> in the .so file --- it may well be that the PG major version number
>> embedded with PG_MODULE_MAGIC is sufficient.

In the .sql file? You mean something like:

ALTER EXTENSION ... SET VERSION '...';

It's currently managed in the .control file of the extension, which
allows us to list available extensions and their version number without
having to parse the .sql script from the C code...

>> Personally I'd forget the notion of major.minor numbers here; all that
>> will accomplish is to complicate storage and comparison of the numbers.
>> We just need a simple integer that gets bumped whenever the extension's
>> SQL script changes.

For contrib, as you wish. Now for third-party extensions, I don't see
us having any authority on what people will use internally in their
companies, etc.

> 1. Identify whether a newer set of SQL definitions than the one
> installed is available. If so, the extension is a candidate for an
> upgrade.

Well, it's currently (WIP in the upgrade branch of my repo) easier than
that, really. You have the control file on the file system and you have
the extension's entry in the catalogs.

http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=shortlog;h=refs/heads/upgrade

What upgrade means here is running a given SQL script, that you choose
depending on the current and next version strings, following a scheme
that has been extensively discussed in another thread, and is currently
implemented like this:

# lo
comment = 'managing Large Objects'
version = '9.1devel'
relocatable = true
upgrade_from_null = 'null => lo.upgrade.sql'

Here, any property that begins with 'upgrade_from_' is considered as an
upgrade setup and the part after the prefix is not considered. The
value is meant to have two parts separated by '=>', first is either null
or a regexp matched against currently installed version number, second
part is the upgrade script name to use at ALTER EXTENSION ... UPGRADE.

We support 'null' version number to be able to "upgrade" from existing
code which is not organized as an extension yet. The aim is to be able
to:

CREATE EMPTY EXTENSION lo; -- version is null here
ALTER EXTENSION lo UPGRADE;

And run a script containing lines that will look like this:

alter domain @extschema(at)(dot)lo set extension lo;
alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
alter function @extschema(at)(dot)lo_manage() set extension lo;

Note that we always need to support the placeholder here, because of
course following dependencies at this point isn't possible.

> 2. Identify whether the installed version of the SQL definitions is
> compatible with the installed shared object. If it's not, we'd like
> the shared library load (or at a minimum, any use of the shared
> library) to fail when attempted, rather than attempting to plunge
> blindly onward and then crashing.

Well, the way I see things, it's already too late and there's nothing we
can easily do to prevent that. What I mean is that the user will
typically upgrade the OS-level package first, then apply the upgrade on
the database(s).

$ apt-get install postgresql-9.1-prefix
$ psql -U postgres -c 'alter extension prefix upgrade' somedb

At the time you tell PostgreSQL about the new extension, the shared
object file has been in place for some time already, and the upgrade SQL
script has not been ran yet.

What I hope extension authors will do is document whether any upgrade
requires a restart or will otherwise be responsible for instability in
the server for backend started with the newer .so before the upgrade
script has been run. So that users/DBA will know whether the upgrade
calls for a maintenance window.

I could see us trying to shoehorn such information into the control file
too, but would ERRORing out on LOAD be any better than taking the
compatibility chance? Knowing that the compatibility in most cases
depends a lot on the actual call paths?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support