Thread: How are CREATE EXTENSION ... VERSION or ALTER EXTENSION ... UPDATE TO ... intended to work?

I've been looking a little more deeply at the extension mechanism,
trying to answer my own question about what happens once there have
been several releases of an extension, and the extensions directory
is now populated with a bunch of files quux--1.0.sql, quux--1.1.sql,
quux--1.0--1.1.sql, quux--1.1--1.0.sql, ..., quux.control.
And somewhere in $libdir there are quux-1.0.so, quux-1.1.so.

The .sql scripts pretty much all CREATE OR REPLACE the function quux()
AS 'MODULE_PATHNAME', 'quux', and module_pathname is set in quux.control.
That file was most recently written when quux-1.1 was installed, so it
defines module_pathname as $libdir/quux-1.1 and the default_version is 1.1.

So it's clear how a plain CREATE EXTENSION quux; works. No question there.
But what is intended to happen if I want to CREATE EXTENSION quux
VERSION 1.0; ?

If there is still a file quux--1.0.sql in extensions/ (which there
may very well be), it will be executed. But it may still contain
CREATE OR REPLACE FUNCTION quux() ... AS 'MODULE_PATHNAME', 'quux'
(which was, after all, correct back when that was the current release)
but now the definition in the current quux.control will cause
MODULE_PATHNAME to expand to quux-1.1.so, not 1.0 as expected.

At least, that's what would happen if each new quux release just ships
new extension files (a new quux.control, new main quux--1.x.sql, and a
few quux--1.y--1.x.sql update files), but does not overwrite the older
quux--1.y.sql files the site may already have in the extensions/ directory.

A workaround could be that each new quux release installer either removes
all pre-existing older quux--*.sql files (so then you would never have
the option to downgrade or create from an older version) ... or overwrites
them all with new versions that hardcode the older shared-object names
instead of using the magic MODULE_PATHNAME. A sort of 'freeze' operation.

That seems to lead to a situation where the *simple* part of the extension
build script is the part that actually builds the extension, along with the
.control file and the new main quux--1.x.sql file (which can just be a
boilerplate with the version injected in two places, and otherwise never
change between releases), while a steadily-growing part of the build script
will only be there to generate overwriting versions of older .sql files
that have their corresponding older module pathnames hardcoded in.

OR ... avoid using module_pathname and just generate every .sql file
with the correct pathname injected ... then older files don't have to
be overwritten when newer versions of the extension are installed, the
previously-installed ones can just be left in place, and they will always
refer to the correct version of the module. That would work, but leave
module_pathname rather useless.

It seems to me that maybe this scheme is missing something like a
%v substitution that can be specified in the .control file as part
of the module_pathname value:

module_pathname = '$libdir/quux-%v'

which the system would expand to the effective version string whenever
substituting MODULE_PATHNAME into any of the .sql scripts.

"Effective version string" would need careful attention; I think if the
upgrade planner picks a sequence of .sql scripts to execute, within each
script in turn, %v needs to expand to the 'destination' version of that
script, that is, the version string that appears last in that script's
filename.

I think with a change like that, there would be less danger that
extension build scripts grow to an unnecessary and awkward complexity
just to deal with curating the collection of .sql scripts associated
with past versions.

Maybe it should be a new keyword, like module_pathname_versioned, just
to avoid changing the meaning of anybody's current module_pathname that
might have a literal %v.

OR ... am I completely overlooking a better way of using the facility
as it now exists?

-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> I've been looking a little more deeply at the extension mechanism,
> trying to answer my own question about what happens once there have
> been several releases of an extension, and the extensions directory
> is now populated with a bunch of files quux--1.0.sql, quux--1.1.sql,
> quux--1.0--1.1.sql, quux--1.1--1.0.sql, ..., quux.control.
> And somewhere in $libdir there are quux-1.0.so, quux-1.1.so.

Well, at least so far as the existing extensions in contrib are concerned,
there are *not* version numbers in the .so filenames.  This means you
can't have more than one version of the .so installed at once, but we've
not really found a need for that.  It's usually feasible --- and desirable
--- to keep ABI compatibility to the extent that the new .so can be
swapped in for the old without needing to change the SQL function
definitions immediately.

I agree that MODULE_PATHNAME isn't an adequate mechanism if you want
to have version numbers in the .so filenames.  We could think about
providing some solution for that, perhaps along the lines of a %v
escape such as you suggest, but it would take awhile for that to get
into the field.  Avoiding MODULE_PATHNAME in favor of writing out
the versioned .so name in the .sql files is probably the path of
least resistance.
        regards, tom lane

On 02/16/16 22:44, Tom Lane wrote:
> Chapman Flack <chap@anastigmatix.net> writes:
>> been several releases of an extension, and the extensions directory
>> is now populated with a bunch of files quux--1.0.sql, quux--1.1.sql,
>> quux--1.0--1.1.sql, quux--1.1--1.0.sql, ..., quux.control.
>> And somewhere in $libdir there are quux-1.0.so, quux-1.1.so.
> 
> Well, at least so far as the existing extensions in contrib are concerned,
> there are *not* version numbers in the .so filenames.  This means you
> can't have more than one version of the .so installed at once, but we've
> not really found a need for that.  It's usually feasible --- and desirable
> --- to keep ABI compatibility to the extent that the new .so can be
> swapped in for the old without needing to change the SQL function
> definitions immediately.

It's true enough that in PL/Java's case, the ABIs / interfaces between
the SQL function definitions and their implementations in the .so have
been quite stable for years, so there might be no immediate problem.
On the other hand, other details of the implementation (bugs come to mind)
do change ... letting a version-specific CREATE EXTENSION load an
unversioned .so could lead to surprises in that area, because it would
/appear to succeed/, meaning pg_extension.extversion would get changed
to what you /thought/ you had loaded (and what you would probably answer,
if asked "what version are you reporting this bug against?"), while what's
actually running could be different, and if I squint I see diagnostic
headaches lying not far around that bend.

> into the field.  Avoiding MODULE_PATHNAME in favor of writing out
> the versioned .so name in the .sql files is probably the path of
> least resistance.

Agreed ... for once maybe I'll follow it.

-Chap