Re: ALTER EXTENSION ... UPGRADE;

Tom Lane <tgl@sss.pgh.pa.us> writes:
> 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.

Yeah, I was picturing a main script that calls other ones by means of  PERFORM
pg_execute_from_file('upgrade_script.sql');

Of course if plpgsql is not to be a requirement, the DO blocks hosting
the CASE logic won't fly and all that blows away.

> 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 both
>     foo_upgrade.11.13.sql
>     foo_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.)

I don't think we can safely design around one part version numbers here,
because I'm yet to see that happening in any extension I've had my hands
on, which means a few already, as you can imagine.

Now, what about having the control file host an 'upgrade' property where
to put the script name? We would have to support a way for this filename
to depend on the already installed version, I'm thinking that %v might
be the easiest here (read: I want to avoid depending on any version
scheme).
 version = '13' script  = 'foo.sql' upgrade = 'foo_upgrade.%v.13.sql'

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