Re: ALTER EXTENSION UPGRADE, v3

All,

Let me summarize the prospective solutions based on some chatting with
some potential extension authors (that is, folks who maintain in-house
stuff they're thinking of offering as extensions). Especially since I
think at this point the majority of -hackers has lost track of the argument:

(A) Writing a separate upgrade script between each two versions
This is completely unrealistic. No module maintainer will ever do this.
Imagine an extension which had 20 releases over 4 years; that would be
*190* upgrade scripts. Any solution we come up with cannot require
module maintainers to write more than one upgrade script per release, or
we will have no module maintainers.

(B) As (A), but with in-script Includes so that eariler versions of
scripts could be re-used for later version upgrades or strung together.
This is somewhat more realistic, given that it could be done
automatically and then tweaked. Especially if we supply a script to
generate version upgrade scripts.

(C) as (A), but through concatinating scripts for upgrade using Make
files. This seems like the worst of all possible solutions. First, it
prevents us from ever having a binary module release network, and
permanently requires anyone using extensions to have GNU make present
and accessible on their system, thus pretty much leaving out the Windows
users forever. Second, it's a lot harder for module maintainers to
tweak than includes would be, especially for SQL-only modules. Third,
it requires Make to check what version you currently have installed in
order to build the module, something which is unlikely to be reliable.

(D) Requires a series of ordered upgrade scripts in sortable version
numbering, each of which gets applied in order between the two versions.
This initially seems like the most attractive option -- and is the one
used by dozens of popular open source web applications -- but has some
major roadblocks for us. First, it requires module authors to subscribe
to a uniform sortable versions scheme (which isn't a bad thing, the
users would certainly appreciate it, and PGXN is liable to enforce this
anyway). Second and much more substantially, .so's installed for later
versions might be incompatible with intermediate upgrade scripts, and
intermediate .so's are unlikely to be present.

(E) Involves relying on pg_upgrade. In addition to the sledgehammer
issue, I really don't see how this would work *at all*. First, modules
would almost by definition have a release cycle which is independant of
PostgreSQL core, and many versions of modules would work with several
versions of PostgreSQL. Second, pg_upgrade is currently unable to
upgrade user-owned objects at all, so I don't see how it would be
handling modules. Thirdly, pg_upgrade does not run scripts, so required
steps for some module upgrades, like say rebuilding an index or
replacing a data type, could not be handled at all. Finally, if we
modify pg_upgrade to handle extensions, we're liable to break it.

Have I summed up the options? Did I miss anything?

Note that handling upgrades of complex applications is not a problem
which anyone in the world has solved that I know of. So it's
unsurprising that we're having difficulty with it.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com