[postgis-devel] Upgrade paths (again)

[Sandro Santilli]

On Fri, Aug 05, 2022 at 07:56:23AM +0200, Sandro Santilli wrote:
> On Mon, Aug 01, 2022 at 08:00:27AM -0400, Greg Troxel wrote:

> > > 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.
> > 
> > It's not clear to me that this is a win.
> 
> Is the new idea explored above making it more of a win to you ?

I've implemented this idea in the current 'upgrade-paths' branch
in the git repository, attached to this pull request:

  https://git.osgeo.org/gitea/postgis/postgis/pulls/104

Following is an example output of calling, on my machine:

	postgis install-upgrade-from-available

Where it ends up installing 40 upgrade path files:

	EXTDIR: /usr/share/postgresql/13/extension
	Installing upgrades from available version 2.5.6dev
	'postgis--2.5.6dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--2.5.6dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--2.5.6dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--2.5.6dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--2.5.6dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 3.3.0dev
	'postgis--3.3.0dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--3.3.0dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--3.3.0dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--3.3.0dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--3.3.0dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 3.1.6dev
	'postgis--3.1.6dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--3.1.6dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--3.1.6dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--3.1.6dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--3.1.6dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 3.0.6dev
	'postgis--3.0.6dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--3.0.6dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--3.0.6dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--3.0.6dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--3.0.6dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 2.4.10dev
	'postgis--2.4.10dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--2.4.10dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--2.4.10dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--2.4.10dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--2.4.10dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 3.2.1dev
	'postgis--3.2.1dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--3.2.1dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--3.2.1dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--3.2.1dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--3.2.1dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 3.2.1
	'postgis--3.2.1--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--3.2.1--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--3.2.1--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--3.2.1--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--3.2.1--ANY.sql' -> 'postgis_topology--TO--ANY.sql'
	Installing upgrades from available version 3.2.2dev
	'postgis--3.2.2dev--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--3.2.2dev--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--3.2.2dev--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--3.2.2dev--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--3.2.2dev--ANY.sql' -> 'postgis_topology--TO--ANY.sql'

This basically installs upgrade paths for EVERY version of "postgis" found available
in the extension dir of PostgreSQL (found via pg_config, so depending on PATH).

If a user upgraded a system triggering REMOVAL of the older postgis version from
the system, there's still a possibility of installing the upgrade path for
version which are not-anymore-available, via another command, example:

	postgis install-upgrade-from 1.0.0
	'postgis--1.0.0--ANY.sql' -> 'postgis--TO--ANY.sql'
	'postgis_raster--1.0.0--ANY.sql' -> 'postgis_raster--TO--ANY.sql'
	'postgis_sfcgal--1.0.0--ANY.sql' -> 'postgis_sfcgal--TO--ANY.sql'
	'postgis_tiger_geocoder--1.0.0--ANY.sql' -> 'postgis_tiger_geocoder--TO--ANY.sql'
	'postgis_topology--1.0.0--ANY.sql' -> 'postgis_topology--TO--ANY.sql'


How does it feel ?

--strk;