Upgrade 2.1.9 to 3.0.0rc1 - can not drop function st_union(geometry) because other objects depend on it

[robe]

I'm not sure this test is fair — dronie is showing this error going from 2.1.9 to 3.0.0rc1

​https://dronie.osgeo.org/postgis/postgis/669

Died at ../run_test.pl line 1588.
failed (Error encountered altering EXTENSION POSTGIS: /tmp/pgis_reg/regress_log)
-----------------------------------------------------------------------------
CREATE EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
HINT: type `SELECT postgis_extensions_upgrade(); to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ERROR: cannot drop function st_union(geometry) because other objects depend on it
DETAIL: view upgrade_view_test depends on function st_union(geometry)
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT: SQL statement "DROP AGGREGATE IF EXISTS ST_Union (geometry)"
PL/pgSQL function inline_code_block line 15 at EXECUTE
-----------------------------------------------------------------------------
make[1]: *** [check] Error 1
make[1]: Leaving directory `/drone/src/regress/core'
make: *** [check-regress] Error 2
make: Leaving directory `/drone/src/regress'
FAIL: upgrade 2.1.9--3.0.0rc1 

Can we not test 2.1.9 as that's long deprecated already and besides we know that ST_Union changed. It's changed I think 3 times since 2.1.9 so this is an expected failure

[strk]

It doesn't look like it changed earlier:

$ grep -A6 'AGGREGATE ST_Union' postgis*/postgis/postgis.sql.in 
postgis-1.5/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (
postgis-1.5/postgis/postgis.sql.in-     basetype = geometry,
postgis-1.5/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-1.5/postgis/postgis.sql.in-     stype = pgis_abs,
postgis-1.5/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-1.5/postgis/postgis.sql.in-     );
postgis-1.5/postgis/postgis.sql.in-
--
postgis-2.0/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (
postgis-2.0/postgis/postgis.sql.in-     basetype = geometry,
postgis-2.0/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-2.0/postgis/postgis.sql.in-     stype = pgis_abs,
postgis-2.0/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-2.0/postgis/postgis.sql.in-     );
postgis-2.0/postgis/postgis.sql.in-
--
postgis-2.1/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (
postgis-2.1/postgis/postgis.sql.in-     basetype = geometry,
postgis-2.1/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-2.1/postgis/postgis.sql.in-     stype = pgis_abs,
postgis-2.1/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-2.1/postgis/postgis.sql.in-     );
postgis-2.1/postgis/postgis.sql.in-
--
postgis-2.2/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (
postgis-2.2/postgis/postgis.sql.in-     basetype = geometry,
postgis-2.2/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-2.2/postgis/postgis.sql.in-     stype = pgis_abs,
postgis-2.2/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-2.2/postgis/postgis.sql.in-     );
postgis-2.2/postgis/postgis.sql.in-
--
postgis-2.3/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (
postgis-2.3/postgis/postgis.sql.in-     basetype = geometry,
postgis-2.3/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-2.3/postgis/postgis.sql.in-     stype = pgis_abs,
postgis-2.3/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-2.3/postgis/postgis.sql.in-     );
postgis-2.3/postgis/postgis.sql.in-
--
postgis-2.4/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (geometry) (
postgis-2.4/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-2.4/postgis/postgis.sql.in-     stype = pgis_abs,
postgis-2.4/postgis/postgis.sql.in-#if POSTGIS_PGSQL_VERSION >= 96
postgis-2.4/postgis/postgis.sql.in-     parallel = safe,
postgis-2.4/postgis/postgis.sql.in-#endif
postgis-2.4/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-2.4/postgis/postgis.sql.in-     );
--
postgis-2.5/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (geometry) (
postgis-2.5/postgis/postgis.sql.in-     sfunc = pgis_geometry_accum_transfn,
postgis-2.5/postgis/postgis.sql.in-     stype = internal,
postgis-2.5/postgis/postgis.sql.in-#if POSTGIS_PGSQL_VERSION >= 96
postgis-2.5/postgis/postgis.sql.in-     parallel = safe,
postgis-2.5/postgis/postgis.sql.in-#endif
postgis-2.5/postgis/postgis.sql.in-     finalfunc = pgis_geometry_union_finalfn
postgis-2.5/postgis/postgis.sql.in-     );
--
postgis/postgis/postgis.sql.in:CREATE AGGREGATE ST_Union (geometry) (
postgis/postgis/postgis.sql.in- sfunc = pgis_geometry_accum_transfn,
postgis/postgis/postgis.sql.in- stype = internal,
postgis/postgis/postgis.sql.in-#if POSTGIS_PGSQL_VERSION >= 96
postgis/postgis/postgis.sql.in- parallel = safe,
postgis/postgis/postgis.sql.in-#endif
postgis/postgis/postgis.sql.in- finalfunc = pgis_geometry_union_finalfn
postgis/postgis/postgis.sql.in- );

So.. maybe Paul just forgot to remove the DROP AGGREGATE line from upgrade_before ?

[pramsey]

No, there is no DROP AGGREGATE ST_Union in postgis_before_upgrade.sql

[pramsey]

On the other hand, this block in postgis_upgrade_for_extension.sql.in seems to drop the aggregate in all cases for Pg < 12.

$postgis_proc_upgrade$
BEGIN
  IF current_setting('server_version_num')::integer >= 120000
  THEN
    EXECUTE $postgis_proc_upgrade_parsed_def$ CREATE OR REPLACE AGGREGATE ST_Union (geometry) (
	sfunc = pgis_geometry_accum_transfn,
	stype = internal,

	parallel = safe,

	finalfunc = pgis_geometry_union_finalfn
	);
 $postgis_proc_upgrade_parsed_def$;
  ELSIF 205 > version_from_num OR (
      205 = version_from_num AND version_from_isdev
    ) FROM _postgis_upgrade_info
  THEN
    EXECUTE 'DROP AGGREGATE IF EXISTS ST_Union (geometry)';
    EXECUTE $postgis_proc_upgrade_parsed_def$ CREATE AGGREGATE ST_Union (geometry) (
	sfunc = pgis_geometry_accum_transfn,
	stype = internal,

	parallel = safe,

	finalfunc = pgis_geometry_union_finalfn
	);
 $postgis_proc_upgrade_parsed_def$;
  END IF;
END
$postgis_proc_upgrade$;

Is your test on the durability of ST_Union a little too optimistic perhaps strk?

[Algunenano]

Isn't this hitting the change done in 2.5 (internal type) and not the one just reverted?

OTOH, I'd be ok with dropping this test. Things work as they should with PG12.

[pramsey]

I've dropped this test in r17882, close this out unless someone can prove to me that we are testing behaviour we ever actually had.

[robe]

Yah I think its a given if you upgrade prior to 12 and you used aggs in your functions, your best bet is to move to 12.