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
(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

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?


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to