[postgis-devel] Upgrade paths (again)

[Sandro Santilli]

On Wed, Sep 14, 2022 at 01:55:53PM -0400, Greg Troxel wrote:
> Sandro Santilli <strk at kbt.io> writes:
> >
> > At *install* time (make install) upgrade scripts are installed to
> > upgrade from each of the versions therein listed to the version being
> > installed.
> 
> Ah, so this is really about "how to upgrade from old.y+1 to new, when at
> the time new was release/built/installed/packaged it only new about old.y".

Yes. 

> What really prevents it is logic built into postgis upgrade that doesn't
> apply the upgrade script to "this version or any later micro even if we
> don't know about it".

Yes. Well, it's partially built into PostgreSQL itself, but the goal here
is to find a workaround to that.
Include also the self-upgrades (PostgreSQL logic prevents applying
upgrade script to "this version if it matches the upgrade target version")

> >   postgis install-extension-upgrades [--pg_sharedir <dir>] [<from>...]
> >                 Ensure files required to upgrade PostGIS from
> >                 the given version are installed on the system.
> >                 The <from> arguments may be either version numbers
> >                 or PostgreSQL share directories to scan to find available
> >                 ones.
> 
> So does that write to paths that are owned by root, and needs to be run
> as root?  The DBA upgrading person has DB privs, and may not be the
> sysadmin.   Or can those go in the current dir, be used, and then be rm'd?

PostgreSQL only allows those upgrade scripts to be in the PostgreSQL
installation directory so tipically only writeable by root.

> The other option is to namespace
> *EVERYTHING* under some postgis-X.Y.Z directories so you can have
> multiple, but I would not go there.

It's actually not an option because PostgreSQL does not allow
extension scripts to be in custom directories, so in order to do
that we'd need to embed the postgis version in the extension name. 

> > The only advantage of generic upgrade paths would be that
> > you could upgrade from any old version to any new version w/out having
> > to install any more upgrade paths ( old -> ANY -> new )
> 
> I don't quite follow how saying you put ANY in the middle is magic.

The magic is that we can trick PostgreSQL into thinking you can go
from version "old" to version "new" by stepping-by version "ANY".
The magic it does is not forcing us to UPDATE pg_extension table.

> I would say that you can allow a script to upgrade from X.Y.Z to be run
> for any X.Y.W where W >= Z, and constrain release branch merges to
> preserve that property.

We DO have a single upstrade script that works for everything.
The only problem we have is that PostgreSQL does not let users run
that single script unless it has a well-defined filename which
includes the version from which you are upgrading and the version to
which you are upgrading. It's a PostgreSQL limitation, not a PostGIS
one. I'm working on a patch to improve that but it will take time
before it's merged (if it's ever merged) so it could still be useful
to have another possibility.

> > The other is that those over 5k files are often not enough to allow me
> > to upgrade
> 
> I don't follow that; is it about new micros of old releases having happened?

Exactly. 5k+ files (and growing) and you still can't upgrade...
Frustrating isn't it ?

--strk;