Upgrade from 3.1.1: ERROR: cannot drop function st_clip(raster,integer,geometry,double precision,boolean) because other objects depend on it

[strk]

Another case of upgrade trouble:

=# select postgis_extensions_upgrade();
NOTICE:  Updating extension postgis from 3.1.1 to 3.4.0
NOTICE:  Updating extension postgis_sfcgal from 3.1.1 to 3.4.0
NOTICE:  Updating extension postgis_raster from 3.1.1 to 3.4.0
ERROR:  cannot drop function st_clip(raster,integer,geometry,double precision,boolean) because other objects depend on it

See also #5484

[strk]

The current definition of that function in raster/rt_pg/rtpostgis.sql.in is:

CREATE OR REPLACE FUNCTION st_clip(
  rast raster, nband integer,
  geom geometry,
  nodataval double precision, crop boolean DEFAULT TRUE
)

The rtpostgis_upgrade_cleanup.sql.in file drops all of these:

DROP FUNCTION IF EXISTS st_clip(raster, integer, geometry, boolean);
DROP FUNCTION IF EXISTS st_clip(raster, geometry, float8, boolean);
DROP FUNCTION IF EXISTS st_clip(raster, geometry, boolean);
DROP FUNCTION IF EXISTS st_clip(raster, int, geometry, float8, boolean);
DROP FUNCTION IF EXISTS st_clip(raster, geometry, float8[], boolean);
DROP FUNCTION IF EXISTS st_clip(raster, integer, geometry, float8[], boolean);

Saying, in a comment, that their signature changed (the comment does not say when and how)

[strk]

Documentation does not say when signature changed: ​https://postgis.net/docs/en/RT_ST_Clip.html

[strk]

All the signatures in current master branch for ST_Clip seem to match the signatures in stable-3.0 so these drops must also be coming from 2.x times or earlier

Support for Replaces comment entered the codebase with 3.1 ([3d947448b9c939b321bc22b85c719845722ecc3d/git]) so we can try fixing this bug with a Replaces coment, although it wasn't specifically written for parameters renames.

Support for parameters renames entered the codebase in 3.5.0dev and backport is not necessarely happening, see ​https://trac.osgeo.org/postgis/ticket/5484#comment:9

[strk]

Same signatures in stable-2.3 as well

The signatures, from 2.3 to current master are:

-- 1
st_clip(
  rast raster,
  nband integer[],
  geom geometry,
  nodataval double precision[] DEFAULT NULL,
  crop boolean DEFAULT TRUE
) RETURNS raster

-- 2
st_clip(
  rast raster,
  nband integer,
  geom geometry,
  nodataval double precision,
  crop boolean DEFAULT TRUE
) RETURNS raster

-- 3
st_clip(
  rast raster,
  nband integer,
  geom geometry,
  crop boolean
) RETURNS raster

-- 4
st_clip(
  rast raster,
  geom geometry,
  nodataval double precision[] DEFAULT NULL,
  crop boolean DEFAULT TRUE
) RETURNS raster

-- 5
st_clip(
  rast raster,
  geom geometry,
  nodataval double precision,
  crop boolean DEFAULT TRUE
) RETURNS raster

-- 6
st_clip(
  rast raster,
  geom geometry,
  crop boolean
) RETURNS raster

It would be about time to consolidate more, now that we support Replaces…

[strk]

PostGIS 2.0.0 is the first PostGIS version having raster support integrated. Differences in st_clip signatures from stable-2.0 to stable-2.1 are:

• signature 1 had the "band" parameter renamed to "nband"
• signature 3 had the "band" parameter renamed to "nband"
• a different signature was removed: st_clip(raster, int, geometry, float8[], boolean);

All other signatures were the same from 2.0.0

[strk]

Ok bug confirmed by ​https://woodie.osgeo.org/repos/30/pipeline/860/9 Next I'll see if the fix is also confirmed

[strk]

I should note that the signature of the function blocking the upgrade did not change in the subject database, so there's really no need to drop it before the upgrade: st_clip(raster,integer,geometry,double precision,boolean)

That drop simply prevents upgrades in presence of views

[Sandro Santilli <strk@…>]

In e1b7db45/git:

Fix upgrades in presence of views using ST_Clip

Includes regress test

References #5488 in master branch (3.5.0dev)

[Sandro Santilli <strk@…>]

In d2687d2/git:

Fix upgrades in presence of views using ST_Clip

Includes regress test

References #5488 in 3.4 branch (3.4.1dev)

[strk]

I'm ok with the backport to 3.4, earlier versions will still have trouble upgrading raster in presence of view. Feel free to reopen if you want a backport.

[Sandro Santilli <strk@…>]

In e94b8f2/git:

Re-add raster upgrade test view dropped in 4fa9e106e

References #5488
References #5496