Re: ALTER EXTENSION UPGRADE, v3 - Mailing list pgsql-hackers

It seems that we've mostly got consensus on the ideas of having a separate
script file for each installable version of an extension, and for each
basic version-upgrade action, with version numbers embedded in the file
names so that the control files don't need to be involved in identifying
what's what.  And the core system is expected to be able to figure out how
to chain upgrade scripts together when necessary.  Therefore, I'm now
ready to start kibitzing on syntax details :-)

First off, I don't much care for the name "CREATE WRAPPER EXTENSION".
WRAPPER is a misnomer in this case --- it's not wrapping anything.
I think Dimitri stated that he chose WRAPPER just because it was an
already existing keyword, but that isn't much of an excuse.

Second, I don't like anything about the term "null version" for the
case of bootstrapping from an old-style contrib module.  Null implies
unknown, which isn't what we've got here --- the upgrade script is going
to make very definite assumptions about what's already there.  Also,
given that we're trying to minimize assumptions about what the version
strings mean, reserving the string "null" for this purpose doesn't seem
like a good idea.  I *definitely* don't want to allow
pg_extension.extversion to ever be a real SQL NULL.

Since we've agreed that there should be a version-to-install option
in CREATE EXTENSION, it seems to me that a workable solution is to
have a special convention for an "empty extension" version name.
Let's suppose that we choose the empty string as this reserved version
name.  Then you would write
CREATE EXTENSION foo VERSION '' [ SCHEMA whatever ];

as the equivalent of CREATE WRAPPER EXTENSION.  This would create the
extension's entry in pg_extension, but not run any script, and the
extension would initially have no members.  After that you could do
ALTER EXTENSION foo UPGRADE TO '1.0';

and this would run the upgrade script "foo--1.0.sql", which would most
likely consist of just "ALTER EXTENSION foo ADD object" commands to
absorb the objects from the old-style contrib module into the extension.

One minor objection to this idea is that "foo--1.0.sql" looks more like a
typo than anything else.  We could alternatively decide that the special
reserved version name is '0', so that bootstrap script names look like
"foo-0-1.0.sql".  But if you don't want to have any built-in assumptions
about what version names mean, you might not like that idea.

Third, I'm also not thrilled with the syntax "ALTER EXTENSION foo
UPGRADE".  UPGRADE isn't an existing keyword (note that VERSION is).
And I don't see any strong reason to assume that the version change
is an "upgrade".  Authors might well choose to support sidegrades or
downgrades, especially with experimental modules.  I suggest either
ALTER EXTENSION foo UPDATE [ TO 'version' ]
ALTER EXTENSION foo VERSION [ 'version' ]

the main excuse for the latter being that it's closer to the comparable
syntax in CREATE EXTENSION.

OK, that's enough bikeshedding for today ...

Comments?
        regards, tom lane