[postgis-devel] Upgrade paths (again)

[Sandro Santilli]

On Sat, Jul 30, 2022 at 06:41:26AM -0400, Greg Troxel wrote:
> 
> Sandro Santilli <strk at kbt.io> writes:
> 
> > In this model we'd install 0-byte files going to an unspecified
> > version (ANY), and a real upgrade script going from an arbitrary
> > version to the current.
> >
> > PostGIS would then always install 2 upgrade paths:
> >
> >     <version>--ANY    ( empty )
> >     ANY--<version>    ( actual upgrade script )
> >
> > The above 2 files would ALWAYS allow hitting our single upgrade script
> > by updating to ANY and then to the target version.
> >
> > The postgis_extensions_upgrade() script, rather than modifying system
> > catalogs as it does now, would simply UPDATE TO 'ANY' prior to UPDATE
> > to the target version (rather than directly touching system catalogs).
> >
> > The problem of NEEDING to install upgrade paths for ANY POSSIBLE
> > postgis extension version installed kind of remains unsolved
> > even if it's more of a packaging issue than a postgis installation
> > issue. What we could do is provide some administration command
> > (re-using the existing "postgis" command, for example?) to install
> > the <version>--ANY upgrade paths for specific arbitrary versions
> > of PostGIS or for those found to be used in databases of a given
> > cluster, something like:
> >
> >   loader/postgis install-upgrade-path-from <version>
> >   loader/postgis install-upgrade-paths-for <database>
> >
> > Comments ? Especially from packagers, as the loader/postgis would
> > then possibly need to be called at upgrade time to figure out what's
> > needed...
> 
> I must confess that I have never really understood this.
> 
> When I read
>   https://postgis.net/docs/manual-3.2/postgis_administration.html#upgrading
> I don't really understand
>   - how that relates to this discussion

This discussion is about how to implement the ability for users to
upgrade their EXTENSION-based PostGIS code in their databases.
We're talking about the procedure documented as "soft" upgrade (ie:
not requiring dump/restore), that is:

  https://postgis.net/docs/manual-3.2/postgis_administration.html#soft_upgrade_extensions

>   - what packaging system should do, if anything, when
>     * replacing postgis 3.1.x with 3.2.x
>     * changing from pgsql 12 with postgis 3.2 to pgsql 13 with postgis 3.2
>     * changing from pgsql 12 with postgis 3.2 to pgsql 13 with postgis 3.3

This is a great question. So far getting rid of everything installed
by old package and installing the new package seems to work, but
requires the new package to install a file for each older postgis
version from which a user is allowed to upgrade from. This currently
means about 400 files if I'm not mistaken, you probably have a better
idea as I assume you have a manifest of what gets installed ?

What I proposed above was NOT installing any of those files but
provide an administration command that would install only the ones
which could be needed (not easy to tell because it depends on what
version of PostGIS extension is found installed in any database served
by a any cluster using a specific PostgreSQL install directory).

>   - what a package manager should do if pgsql is not running when the
>     above upgrades happen  (pkgsrc typically doesn't do a lot of this,
>     and leaves the user to dump/restore across postgresql version
>     changes, i.e. dump, nuke db dir, upgrade, restore.

With my proposal it's all to be defined and your input is welcome.
With current situation there's no need for pgsql to be running during
system upgrades.

>   - how many people have postgis installed "without extensions" vs
>     "with"

We don't spy users so can't really tell but I suspect 99.9% of users
are using postgis "as an extension".

>   - how to change from "without" to "with"

Calling `postgis_extensions_upgrade()' in a database changes from "without"
to "with". This is a per-database setup.

Going from "with" to "without" is a bit harder. There's a perl script
in the repository to create an "unpackager" SQL script, that we use
to unpackage "postgis_raster" when upgrading form PostGIS version <
3.0.0:

  utils/create_extension_unpackage.pl

I filed a ticket 5 years ago to provide an SQL function to do this
[ postgis_extension_unpackage() ]
but for some reason it was closed (without a comment) 3 years ago.
Darafai: do you recall why ?

  https://trac.osgeo.org/postgis/ticket/3918

>   - what to type to find out which you have

The postgis_full_version() output will contain an [EXTENSION]
label if you're running in EXTENSION mode, and contain warnings
in case you have a mix of extension and non-extension postgis provided
modules (topology, raster, sfcgal, ...)

Alternatively, and "manually" you can use system catalogs like:

  select extname, extversion from pg_extension where extname like 'postgis%';

The manual approach won't tell you which non-extension based modules
of postgis you have installed so the recommended way is the
`postgis_full_version()` call.


> If what you mean is:
>   - we'll have the same set of (increasing number of ) files
>   - most of them will be 0-byte files
> 
> then for me it is an unimportant change.  Right now a few of them have
> content and many are symlinks.

That's the first change I'm proposing, but I'm foreseeing a setup
in which we STOP installing "an increasing number of files" but
rather provide an administrative command to install them on demand.

> I realize Windows doesn't really support symlinks in a reasonable way,
> and presumably this "duplicated files" is a Windows-only concern.  If
> that's off base, please straighten me out.

You're right that the "duplicated files" is a windows only issue,
but I'm also concerned about increasing number of symlinks...

--strk;