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

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> 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.

Hmm ...

I don't believe that extension SQL scripts should rely on DO blocks.
There is no requirement that plpgsql be installed, and we're not going
to create one as part of this feature.  What this means is that the
design you offer above doesn't work at all, since it fundamentally
assumes that the SQL script can do conditional logic.  What's more,
it fundamentally assumes that the script WILL do conditional logic
and support (in one lump) every possible combination of versions.
That's going to turn into buggy spaghetti-code very quickly.

I think that something that could work is more along the lines of the
extension containing different upgrade scripts for whatever set of cases
the author feels like supporting; for example the foo extension might
provide bothfoo_upgrade.11.13.sqlfoo_upgrade.12.13.sql
if the author is willing to support one-step upgrades from two preceding
versions to version 13.  It would then be the responsibility of the
ALTER EXTENSION code to select and execute the correct upgrade script.
A missing script would be reported as an upgrade failure by ALTER
EXTENSION.

(Actually, we could probably assume that the target version is
implicitly "the current version", as identified from the control file,
and omit that from the script file names.  That would avoid ambiguity
if version numbers can have more than one part.)
        regards, tom lane