Opened 4 years ago

Closed 4 years ago

#4523 closed defect (fixed)

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

Reported by: robe Owned by: strk
Priority: medium Milestone: PostGIS 3.0.0
Component: build Version: master
Keywords: Cc:

Description (last modified by 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

Change History (7)

comment:1 by robe, 4 years ago

Description: modified (diff)

comment:2 by strk, 4 years ago

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 ?

comment:3 by pramsey, 4 years ago

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

comment:4 by pramsey, 4 years ago

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?

comment:5 by Algunenano, 4 years ago

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.

comment:6 by pramsey, 4 years ago

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.

comment:7 by robe, 4 years ago

Resolution: fixed
Status: newclosed

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.

Note: See TracTickets for help on using tickets.