Re: ALTER EXTENSION UPGRADE, v3

On Wed, Feb 2, 2011 at 12:31 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> They are identical except for the extra line in the second one. If I had, say 15 different versions of an extension, then I'd have 15 upgrade scripts. That's fine. But in your plan, the script to upgrade from version 1 to version 15 would have all the same code as the v14 script, plus any additional. The v14 script would have everything in v13. v13 would have everything in v12. With no support for the equivalent of psql's \i, that's extremely redundant and a huge PITA to maintain. Hence my hate.

Stepping back from the implementation details and file naming
conventions a bit, it seems to me that when you do schema upgrades,
there are basically three possible things you might want to put in the
upgrade script:

1. SQL statements that you want to execute unconditionally, such as
(1a) CREATE OR REPLACE FUNCTION on something that has a compatible
signature in every prior release in which it exists, or (1b) CREATE
TABLE on a table that was added in the most recent release.
2. SQL statements that you want to execute if the version we're
upgrading *from* is older than X. For example, CREATE TABLE on a
table that was added in version 6 should be executed if we're coming
from a version less than 6, and skipped otherwise.
3. SQL statements that you want to execute if the version we're
upgrading *from* is between X and Y. This is less common, but you
sometimes need it. For example, in version 6 you added a table, but
by version 13 it wasn't needed any more so you removed it. The
upgrade script for version 17 should drop the table if we're coming
from a version between 6 and 12 (if we're coming from pre-6, it was
never created to begin with, and we don't want to drop an unrelated
table with the same name, and if we're coming from 13-16, it either
never existed or, depending on the history, some previous upgrade
dropped it).

So how could we provide this functionality? Dimitri's approach is
simple in concept, but it potentially requires a LOT of bookkeeping
when an extension has been around for a while, to make sure that all
of the upgrade files contain exactly the right combinations of stuff.
I've managed schema upgrades that went through dozens of versions, and
making sure that you can correctly upgrade from every previous version
to v48 is definitely going to be a challenge. David's approach makes
that a little simpler in some ways, but I think it falls down pretty
badly on point #3.

I'd actually be inclined to just have ONE upgrade file and invent some
kind of meta-language for controlling which statements get executed.
Just to pick a syntax that everyone will probably hate:

[..]
-- unconditional stuff

[..6]
-- stuff to do if coming from pre-7

[..]
-- some more unconditional stuff

[6..12]
-- stuff to do if coming from between 6 and 12

[..]
-- a few more unconditional things

You might all be either scoffing right now or laughing so hard there
are tears running down your face, but in my not insignificant
experience that's what real schema upgrade scripts need to cope with
in real-world situations, so I hereby pre-reject any comments of the
form "that should never be necessary in real life because..." and/or
"for that to be necessary you'd have to have done the following
bat-shit stupid thing".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company