[postgis-devel] Upgrade paths (again)

[Greg Troxel]

Sandro Santilli <strk at kbt.io> writes:

> On Wed, Sep 14, 2022 at 11:04:00AM -0400, Greg Troxel wrote:
>> Sandro Santilli <strk at kbt.io> writes:
>> 
>> > I've changed the 'upgrade-paths' code so to not require PostgreSQL at
>> > all but keep the current model of installing explictly stated upgrade
>> > paths from old supported versions. The new aim is simply avoiding the
>> > need to run updates on system catalogs.
>> 
>> Who is explicitly stating this and when?
>
> PostGIS source tree is explictly stating it in:
>
>     extensions/upgradeable_versions.mk
>
> 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".

>> Building a binary package has to have the same outcome whether or not
>> postgis has ever been set up or in varying versions on the build
>> machine.
>
> This is what you get now and what you'll keep getting with the
> current upgrade-paths branch (as of commit e1efd3d6c8).

ok; that's what matters.

>> Anything that isn't in the binary package isn't available to the user.
>
> And this is what prevents upgrades from releases which came out
> AFTER the release of PostGIS found in your database (which aren't
> necessarely downgrades, if you by policy kept tracking a stable
> branch).

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".  That being sane and a policy to limit micros to
bugfixes go together.

>> Are you saying you want to pivot from:
>> 
>>   have the build/install process (and thus what is packaged) install
>>   upgrade scripts from N old versions to the current version
>> 
>> to
>> 
>>   have the build/install process install some proto-upgrade-scripts and
>>   also some program (perhaps a shell script) that, can be run something
>>   like
>> 
>>     postgis-generate-upgrade-from 2.5.1
>> 
>>   and will output the upgrade script?
>
> Yes, this is what I'm proposing.
> The "upgrade-path" contains such script (in perl), currently having
> this syntax:
>
>   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?

>>  Or also run it?
>
> In its current state the "upgrade-script" is also running it, passing
> it the hard-coded list found in "upgradeable-versions.mk", but this
> means the *same* files would be installed by running `make install`
> from multiple versions of PostGIS, and thus the same binary packages
> would contain a lot of the same files. I'd evaluate whether to avoid
> doing this part.

I see; so not sure that this saves.

>>  Or maybe just
>>   query all the databases and do it, but it has to be a pgsql superuser,
>>   or only upgrade databases it has privs on?
>
> In its current incarnation, the `postgis install-extension-upgrades`
> does not implement querying databases. Could be a useful addition.

ok

>> From the packaging point of view, if you want to change the upgrade
>> process it doesn't bother me.  But the "make && make install" cannot
>> look into anything about the current system.  That breaks reproducible
>> builds (not claiming we are fully there but a change in the set of
>> installed files based on the current state is a bug) and will make the
>> resulting package non-portable.
>
> Ok, so the current state of the branch is still good for you (doesn't
> query the system).

great.  sorry if I'm being thick.

>> > The new packaging problem with this model is that the SAME file would
>> > be included in multiple packaged postgis versions, namely upgrade
>> > paths in the form:
>> >
>> >     postgis--3.0.0--ANY.sql
>> 
>> I don't understand why that's a problem.  That file, for packaged
>> version N, is how to upgrade from 3.0.0 to N.  It has the same name, and
>> different contents, for every value of N.  That's true of most files in
>> a package and it isn't a problem.
>
> Maybe you didn't understand, I'm not using variables in that filename,
> it's the actual name: `postgis--3.0.0--ANY.sql`.

I did understand that.

> The file with the SAME name would contained by all future versions of
> postgis IF we implement this approach of providing "generic upgrade
> paths" (<old_known_version>--ANY) instead of the specific ones
> ( <old_known_version>--<package_version> ).

sure, but that's ok.   What I meant is that each version of postgis
installs a set of files, and the content can be different.  There is no
requirement for same content, non-conflicting pathnames, as only one
version can be installed at once.   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.

> Why would we want "generic upgrade paths" ? The idea was that each
> version would install the "<this_version>--ANY" upgrade path, but
> given the scenario you described ( files in old packages are removed
> before files in new package are installed ) this is now less
> interesting. 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.

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.

>> I don't understand why you don't want to install them, other than that
>> there are many of them.
>
> That's the main reason:
>
>   # ls -l `pg_config --sharedir`/extension/{postgis,address_standardizer}* | wc -l
>   5222

ok, that's a good reason.

> The other is that those over 5k files are often not enough to allow me
> to upgrade, because we maintain stable branches and so at some point
> old databases end up being in a stable version which cannot be
> upgraded to a newer major release, so I have to do create a bunch of
> symlinks (or, with the new code call a script) in any case.

I don't follow that; is it about new micros of old releases having happened?

>> If you don't, then what is a DBA supposed to do?  Where are they going
>> to get the upgrade scripts from?
>
> The `postgis` script will take care of that, they just need to state
> which versions they want to allow users to upgrade from.

that's ok then.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220914/69e1633d/attachment.sig>