Can't upgrade from 3.0.0alpha3 to 3.1.0dev (cannot drop function pgis_geometry_union_transfn)

[strk]

strk=# select postgis_full_version();
POSTGIS="3.1.0dev r3.1.0alpha1-7-g9a939fdae" [EXTENSION] PGSQL="96" GEOS="3.9.0dev-CAPI-1.14.0" SFCGAL="1.3.6" PROJ="7.0.0" GDAL="GDAL 3.0.4, released 2020/01/28" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" (core procs from "3.0.0alpha3dev r17535" need upgrade) TOPOLOGY (topology procs from "3.0.0alpha5dev r17729" need upgrade) [UNPACKAGED!] RASTER (raster procs from "3.0.0alpha3dev r17535" need upgrade) (sfcgal procs from "3.0.0alpha3dev r17535" need upgrade)
strk=# ALTER EXTENSION postgis UPDATE TO "3.1.0dev";
ERROR:  cannot drop function pgis_geometry_union_transfn(internal,geometry) because other objects depend on it
DETAIL:  function st_union(geometry) depends on function pgis_geometry_union_transfn(internal,geometry)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

See also #4483 (please let's fix upgrade issues!)

[Algunenano]

A comment: Both #4483 and, as far as I remember, this come from using alpha releases and skipping intermediates. I don't think it's worth expending too much effort supporting this nor I consider them blockers (as long as stable -> stable works I'm fine).

[strk]

I believe free software developers should try as much as possible to lower the barrier of entry for contributors. Running an intermediate version of PostGIS is something developers often end up doing, and we want their life to be easy (well, I want my life to be easy).

Now, what do you mean by "skipping intermediates" ? Why are we wanting to drop a pgis_geometry_union_transfn function before (or without?) st_union is dropped ? Is this somehow related to #4386 ?

[strk]

The function pgis_geometry_union_transfn is removed by a line in postgis/postgis_after_upgrade.sql - a comment above the drop says: -- dev function 3.0 cycle meaning of which is unclear to me.

If it was a temporary function, added during development, why is it still used/referenced ?

[strk]

According to a comment in postgis/postgis.sql.in, the ST_Union(geometry) aggregate was last changed in 2.5.0 (to use internal type), but as the _current_ definition of it does NOT make use of pgis_geometry_union_transfn anymore (but rather uses pgis_geometry_accum_transfn ) the comment must be wrong !

[strk]

Commit [75a044c6141b64ed3357e766cdb48f7b40bd4867/git] restored old signature to avoid a DROP of the aggregate on upgrade, but leaving the interim installs (like mine) out in the dark (cannot upgrade, due to the lack of drop). We'd need a better strategy to deal with these cases.

[strk]

A possible option could be: drop CASCADE the function _before_ the upgrade, and make sure AGGREGATEs are added if missing ( no matter _when_ they were changed )

[Algunenano]

Now, what do you mean by "skipping intermediates"

I meant that you are trying to go from alpha3 -> 3.1, not alpha3 -> alpha4 -> alpha5 -> 3.1, so you are facing issues that might have been resolved in the upgrade processes in between. The upgrade matrix grows quadratically with new releases, so supporting also upgrading from alphas indefinitely is madness in my book (unless we get contributors quadratically too).

A possible option could be: drop CASCADE the function _before_ the upgrade, and make sure AGGREGATEs are added if missing ( no matter _when_ they were changed )

This raises a huge red flag to me. Under no circumstances an extension should drop objects that weren't created by it. Doing a cascade drop could mean deleting views, indexes or other user created elements. That isn't an acceptable behaviour.

[strk]

I tried installing postgis from the stable-3.0 branch. Same problem:

psql:postgis/postgis_upgrade.sql:7634: ERROR:  cannot drop function pgis_geometry_union_transfn(internal,geometry) because other objects depend on it
DETAIL:  function st_union(geometry) depends on function pgis_geometry_union_transfn(internal,geometry)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Upgrade info:

strk=# select * from _postgis_upgrade_info;
 scripts_upgraded |   scripts_installed   | version_to_num | version_from_num | version_from_isdev 
------------------+-----------------------+----------------+------------------+--------------------
 3.0              | 3.0.0alpha3dev r17535 |                |              300 | t
(1 row)

The upgrade procedure will ONLY drop/re-create the aggregate IFF coming from 2.5 (because the comment says that the aggregate was ONLY changed in 2.5, not in 3.0).

If we dont support upgrades from alpha we should really not ship alphas at all. How can people possibly test if upgrading to an alpha can put them in a situation to be impossible to upgrade ?

Really, we need to fix this. If the AGGREGATE signature changed at some point, we need to encode that it changed.

[Algunenano]

If we dont support upgrades from alpha we should really not ship alphas at all.

If shipping alphas means that we need to support them, then I'll -1 every alpha/beta/rc release from now on. AFAIK, maybe I'm wrong, Postgis has never offered support for development releases. We currently don't support 3.0.0alpha3, in the same exact way we don't support 2.2.

If you generate currently build 3.1 you don't get an upgrade path from 2.5.0alpha, 2.5.0beta1... to 3.1 and I think that's correct and the way to go. In fact, I think that we should have already removed the upgrade paths from 3.0.0alphaX to 3.1 because it's not supported and you are not supposed to do it.

IMO, there are 3 kinds of upgrades:

• Stable to stable: Should always work no matter what for supported releases. Best effort for unsupported releases.
• Alpha/Beta/RC to next stable: Should work but it's also a best effort, that is, they can't be a blocker in any way. If you find an issue you should be able to report it and fix it, or decide it's not worth it and nuke the database.
• Alpha/Beta/RC to other stable releases (not the immediate one after those): There isn't a upgrade path available. You should go to the nearest stable first and upgrade from there.

[strk]

This patch fixes the upgrade for me:

diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 8d1c802bf..dd4f87a09 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -3900,6 +3900,7 @@ CREATE OR REPLACE FUNCTION ST_Union (geometry[])
 -- we don't want to force drop of this agg since its often used in views
 -- parallel handling dealt with in postgis_after_upgrade.sql
 -- Changed: 2.5.0 use 'internal' stype
+-- Changed: 3.0.0 step by pgis_geometry_union_transfn
 CREATE AGGREGATE ST_Union (geometry) (
        sfunc = pgis_geometry_accum_transfn,
        stype = internal,

Of course it forces a DROP/RESTORE of the aggregate for those who did NOT step to an alpha version, which is not ideal.

[strk]

Besides, if we don't support upgrades from alpha versions... why are we attempting to DROP that transition function at all ? We should just let it stay there ?

[pramsey]

They can dump and restore. Why are they testing an alpha on a production system that requires in-place upgrade? Let's just close this out.