[postgis-devel] Upgrade paths (again)

[Julien Rouhaud]

Hi,

On Sat, Jul 30, 2022 at 08:13:58AM +0200, Sandro Santilli wrote:
> We're still in need to find a good solution
> for the ever-growing number of upgrade path
> files we're installing on the system:
>
>   https://trac.osgeo.org/postgis/ticket/5092
>
> The currently proposed solutions are in:
>
>   https://trac.osgeo.org/postgis/wiki/PostGISExtensionPaths
>
> And can be summarized here as:
>
>   SOLUTION1 -- modify system catalog
>   SOLUTION2 -- patch PostgreSQL
>   SOLUTION3 -- 0-bytes model
>
> Regina found altering system catalogs from
> postgis_extension_upgrade() is not always
> supported, invalidating SOLUTION1:
>
>   https://trac.osgeo.org/postgis/ticket/5194
>
> Patching PostgreSQL is still a long way ahead of time
> so we're left with of SOLUTION3, which is the 0-bytes model.

Have you considered an approach that's somewhat mixing SOLUTION1 and SOLUTION2?

If the only problem with SOLUTION1 is the possible error due to a lack of
permission, why not do that in C in the backend, intercepting the ALTER
EXTENSION command?  This would work even if a non superuser runs the command.
That's a bit hackish but it still looks a bit better than the SOLUTION3.

The big downside with that approach is that it only works if postgis has been
loaded in the backend (either via the session_preload_libraries or similar, an
explicit LOAD which needs to be superuser or indirectly if any postgis C
function has been previously used), otherwise it would fail with a misleading
"extension postgis has no update path from version X to version Y".  But the
postgis_extensions_upgrade() function could be modified to make sure it first
load postgis, one way or another, before trying to update the extension, so
maybe it wouldn't that big of a problem.

As an example, I attaching a quick POC for a dummy "pg_ext_hook" extension
(needs pg14 or more, but that's easy to fix for previous versions).  It doesn't
require superuser privilege, installs by default to version 1.0.0, and provides
2 upgrade scripts:

- from ANY to 2.0.0, with some basic DDL and adds a line in pg_ext_hook table
- from ANY to broken, which fails and is just there to make sure that the
  "ANY" extension version is rollbacked as expected

It also provides a dummy "pg_ext_hook_load()" function that does nothing, but
will load the module as it's a C function, so it can easily be tested with a
non-superuser.  There are also basic regression tests to validate the behavior.

Note that you can upgrade to 2.0.0 even if it's already the current version
(which can be seen with the new line added in the table each time).  My
understanding is that it's wanted, as if you upgrade postgres to a newer
version but keep the same postgis version, you might still be able to get new
features thanks to the newer pg version.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pg_ext_hook.tgz
Type: application/octet-stream
Size: 2976 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220914/3e82663f/attachment.obj>