[postgis-devel] Proposal for PostgreSQL enhanced extension upgrade handling

[Sandro Santilli]

Following the thread about the hack of postgis_extensions_upgrade
tagging the current version as "ANY" to reduce dependency on
specific upgrade scripts we install on the system [1] I'm writing
this mail to propose a possible upstream enhancement of the mechanism
by which PostgreSQL looks for upgrade scripts, to help us with keeping
the number of installed files as low as possible (ideally: 1 single
file, as matching our actual upgrade script).

At the moment PostgreSQL checks the current version of an extension
and looks for files in the form:

  ${extension_name}--${current_version}--${other_version}

And chains them to find a path going from ${current_version}
to ${target_version}.

The proposal is to allow wildcards in the filenames so that
${current_version} doesn't only match ${current_version} but
also matches some more generic string.

An example wildcard which could work for us would be for
example the string '%', which could match "any-sequence-of-characters"
(deriving its meaning from the SQL standard used for LIKE.

If PostgreSQL supported that, we could ship, with any new version
of PostGIS (call it ${version}) only these two files:

  - postgis--%--ANY.sql
    A 0 bytes file

  - postgis--ANY-${version}.sql
    Our current single-file upgrade script

The enhanced syntax would not only work for PostGIS but would open
up the possibility for other extension to have upgrade scripts
which are valid for a set of versions, for examples:

  - myextension--0.%--1.0.sql
  - myextension--0.2.%--0.3.1.sql

The wildcard, in this proposal, should be only accepted in
the "from" part of the filenaming.

What do you think ? Could such proposal be submitted upstream ?

[1] https://trac.osgeo.org/postgis/ticket/5092

--strk; 

  Libre GIS consultant/developer
  https://strk.kbt.io/services.html