ALTER EXTENSION ... UPGRADE; (was: Extensions, patch v16)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Why would you choose to maintain it in the Makefile? In most cases
> makefiles are the least likely thing to be changing during a minor
> update.

I must have a packager skewed view of things here, but ok, point noted.

> I would think that the right place for it is in the C code
> (if we're trying to version .so files) or the .sql file, if we're trying
> to version the SQL objects. In particular, if the only reason the
> makefile needs to know it is to inject it into the control file, it
> seems completely silly to not just maintain it in the control file
> instead.

If we are after the a bare feature set, sure, the Makefile facility is
only about trying to be nice to the user. I accept that you think it's
counter productive rather than helping.

In the next patch, I will rename the control files to be straight
.control files, remove the implicit rule in the pgxs.mk file, remove the
note in the documentation and apply the following:

sed -i 's/EXTVERSION/9.1devel/' contrib/*/*.control

Or do we want contrib's specific version numbers that are not all the
same as the current PostgreSQL version number?

On to your question about the upgrade design, in order not to paint
ourselves into a corner. What I now have in mind is the following:

When there's an extension upgrade the user will have to install the new
files (.so, .sql, .control) and run an upgrade command in his databases:

ALTER EXTENSION pair UPGRADE;

The version we upgrade from is known from the catalog, the version we
upgrade to is read in the control file. So we are able to call the sql
script and offer a way for it to know about the versions. The simplest
way seems to be a new pair of functions:

pg_extension_upgrade() returns bool
pg_extension_versions() returns table(current text, next text)

Those are to be run only from the extension's script.

The first returns false when the user did CREATE EXTENSION and true when
the user did ALTER EXTENSION UPGRADE, which are the only two commands
that will run the script.

The second will return the versions we detailed above, and the
extension's author is free to compare them however he wants to and
decide what to do now. It's cool that we have DO blocks here, and
pg_execute_sql_file() to offer the same facility as \i for psql scripts.

Of course if calling the script succeeds, then the version number in the
pg_extension catalog is changed to the "next" one.

Now, it would be better if it were easy to compare version numbers, for
example with a -core datatype that handles that. Do we already want to
open this can of worms?

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