Re: ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 3:46 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Feb 10, 2011, at 12:07 PM, Robert Haas wrote:
>
>> You don't need them to be sortable.  You just need them to be
>> comparable, and equality seems like a plenty good enough comparison
>> rule.  You can compute the shortest chain of upgrade scripts that can
>> take you from the current version to the target version.
>
> You have to be able to apply them in order. Unless I'm missing something, that means you need to be able to sort them.

Not at all. Say the currently installed version of the "dungeon"
extension is "kobold" and you want to upgrade to "bugbear". You have
the following scripts:

dungeon-goblin-orc.sql
dungeon-hobgoblin-bugbear.sql
dungeon-kobold-goblin.sql
dungeon-orc-hobgoblin.sql

Now, it's pretty clear that the only way to get to bugbear is to come
from hobgoblin, and the only way to get to hobgoblin is to come from
orc. orc can be reached only from goblin, which can be reached only
from kobold. So it's 100% clear that you have to apply the scripts in
the following order:

dungeon-kobold-goblin.sql
dungeon-goblin-orc.sql
dungeon-orc-hobgoblin.sql
dungeon-hobgoblin-bugbear.sql

Note that this even works if the versions aren't totally ordered. For
example, suppose you release version 0.1 of a module and later you
release a 1.0, which unfortunately is incompatible: there's no upgrade
path from 0.1 to 1.0. In time, 1.0 is superseded by 1.1. And then
you make some improvements to the old 0.1 code base and release that
as 0.2. Finally, you come up with an idea for unifying the two and
release a 1.2 version, which supports upgrades from all the previous
versions. You just ship:

foo-0.1-0.2.sql
foo-0.2-1.2.sql
foo-1.0-1.1.sql
foo-1.1-1.2.sql

If the user asks to upgrade to version 1.2, we'll observe that you can
get to 1.2 from 1.1 or from 0.2. Not knowing what the version numbers
mean, we'll look a bit further and see that you can get from 1.0 to
1.1 or from 0.1 to 0.2. Thus you can get to 1.2 like this:

0.1 -> 0.2 -> 1.2
0.2 -> 1.2
1.0 -> 1.1 -> 1.2
1.1 -> 1.2

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