Re: ALTER EXTENSION UPGRADE, v3

I spent some time reviewing this thread. I think the major point that's
not received adequate discussion is this: the design assumes that there's
just one "current version" of any extension, and that's not good enough.
David Fetter was trying to make that point upthread but didn't seem to
convince people. I'm convinced though. I think that one of the major
selling points of extensions could be having a controlled way of exposing
different versions of an API and letting users select which one is in use
in each database. Look at how much effort we spend maintaining back
branches of the core code for people who don't want to, eg, update their
apps to avoid pre-8.3-style implicit casting. (Yeah, I know that on-disk
compatibility is another major reason for staying on a back branch, but
API changes are definitely part of it.)

So I believe that it'd be a good idea if it were possible for an extension
author to distribute a package that implements, say, versions 1.0, 1.1,
and 2.0 of hstore. Not all will choose to do the work needed for that, of
course, and that's fine. But the extension mechanism ought to permit it.
Over time we might get to a point where somebody could be running the
latest version of the core database (with all the bug fixes and other
goodness of that) but his application compatibility problems are solved
by running back-rev versions of certain extensions.

To do this, we need to remove the concept that the control file specifies
"the" version of an extension; rather the version is associated with the
SQL script file. I think we should embed the version number in the script
file name, and require one to be present (no more omitted version
numbers). So you would distribute, say,
hstore-1.0.sql
hstore-1.1.sql
hstore-2.0.sql
representing the scripts needed to install these three versions from
scratch. CREATE EXTENSION would have an option to select which
version to install. If the option is omitted, there are at least two
things we could do:
1. Choose the newest available version.
2. Let the control file specify which version is the default.
I think I prefer #2 because it avoids needing a rule for comparing
version identifiers, and it caters to the possibility that the "newest"
version isn't yet mature enough to be a good default.

As for upgrades, let's just expect upgrade scripts to be named
extension-oldversion-newversion.sql. ALTER EXTENSION UPGRADE knows the
relevant oldversion from pg_extension, and newversion can be handled the
same way as in CREATE, ie, either the user says which version to update to
or we use the default version from the control file.

I don't seriously expect most extension authors to bother preparing
upgrade scripts for any cases except adjacent pairs of versions.
That means that if a user comes along and wants to upgrade across several
versions of the extension, he'll have to do it in several steps:
ALTER EXTENSION hstore UPGRADE TO '1.1';
ALTER EXTENSION hstore UPGRADE TO '2.0';
ALTER EXTENSION hstore UPGRADE TO '2.1';
I don't see that as being a major problem --- how often will people have
the need to do that, anyway? Authors who feel that it is a big deal can
expend the work to provide shortcut scripts. I do not see adequate return
on investment from the regexp-matching complications in the currently
submitted patch.

In this scheme, all the extension scripts are independent. We spent quite
a lot of time arguing about ways to avoid duplication of code between
scripts, but frankly I'm not convinced that that's worth troubling over.
As far as the initial-install scripts go, once you've released 1.0 it's
unlikely you'll ever change it again, so the fact that you copied and
pasted it as a starting point for 1.1 isn't really a maintenance burden.
Version upgrade scripts won't share any code at all, unless the author is
trying to provide shortcut scripts for multi-version jumps, and as I said,
I doubt that many will bother. Also, it'll be some time before there's
much need for multi-version update scripts anyway, so I am not feeling
that it is necessary to solve that now. We could later on add some kind
of script inclusion capability to allow authors to avoid code duplication
in multi-version update scripts, but it's just not urgent.

So, concrete proposal is to enforce the "extension-version.sql" and
"extension-oldversion-newversion.sql" naming rules for scripts, which
means getting rid of the script name parameter in control files.
(Instead, we could have a directory parameter that tells which directory
holds all the install and upgrade scripts for the extension.) Also, the
"version" parameter should be renamed to something like "current_version"
or "default_version". We also have to be wary of whether any other
control-file parameters specify something that might be version-specific.
Looking at the current list:

comment: probably OK to consider this as a default for all versions.
We already have the ability for any script file to override it, anyway.

encoding: I don't see any big problem with insisting that all scripts for
a given extension be in the same encoding.

requires, relocatable and schema: These are problematic, because it's not
out of the question that someone might want to change these properties
from one version to another. But as things are currently set up, we must
know these things before we start to run the extension script, because
they are needed to set up the search_path correctly.

Perhaps for now it's sufficient to say that these properties can't change
across versions. Alternatively, we could allow there to be a secondary
version-specific control file that can override the main control file.
IOW, we'd read "extension.control" to get the directory and
default_version values, then determine the version we are installing or
upgrading to, then see if there's an "extension-version.control" file
in the extension's directory, and if so read that and let it replace
the remaining parameters' values.

Comments?

regards, tom lane