ERROR: cannot drop function st_value(raster,geometry,boolean) because other objects depend on it

[strk]

=# select postgis_extensions_upgrade();
NOTICE:  Updating extension postgis from 3.1.1 to 3.4.0
NOTICE:  Extension postgis_sfcgal is not available or not packagable for some reason
NOTICE:  Updating extension postgis_raster from 3.1.1 to 3.4.0
ERROR:  cannot drop function st_value(raster,geometry,boolean) because other objects depend on it
DETAIL:  view helmer_tmp.tracking_hr_with_rst_v depends on function st_value(raster,geometry,boolean)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT:  SQL statement "ALTER EXTENSION postgis_raster UPDATE TO "3.4.0";"
PL/pgSQL function postgis_extensions_upgrade() line 79 at EXECUTE

[strk]

WIP pull request here: ​https://git.osgeo.org/gitea/postgis/postgis/pulls/138

[strk]

The ST_Value function has undertaken a few revisions but was never given the Replaces comment, and also lacks other availability/changed comments.

We currently define:

st_value(rast raster, band integer, x integer, y integer, exclude_nodata_value boolean DEFAULT TRUE) — Changed: 3.2.0 added resample arg st_value(rast raster, band integer, pt geometry, exclude_nodata_value boolean DEFAULT TRUE, resample text DEFAULT 'nearest') st_value(rast raster, x integer, y integer, exclude_nodata_value boolean DEFAULT TRUE) st_value(rast raster, pt geometry, exclude_nodata_value boolean DEFAULT TRUE)

Chances are a Replaces comment going with the Changed line would suffice, but unfortunately I've troubles building both 3.1 and 3.4 with the same proj version to test upgrades myself.

[strk]

Actually, comparing the ST_Value signatures in 3.1 and 3.4 branches I fail to see why the version taking (raster,geometry,boolean) should be drop, as we have exactly the same signature in both 3.1.1 and 3.4.0:

st_value(rast raster, pt geometry, exclude_nodata_value boolean DEFAULT TRUE) RETURNS float8

[strk]

We have `DROP FUNCTION IF EXISTS st_value(raster, geometry, boolean); ` in rtpostgis_upgrade_cleanup.sql.in which is going to happen *before* re-creating that function. There's no trace as WHY this pre-upgrade is done. Maybe argument names were changed, but no comment says when.

This cleanup step is also found in 3.1.1 so the rename of arguments must be older than 3.1

[strk]

It looks like the name of the third (boolean) argument was changed in 2.1:

• stable-2.0: st_value(rast raster, pt geometry, hasnodata boolean DEFAULT TRUE)
• stable-2.1: st_value(rast raster, pt geometry, exclude_nodata_value boolean DEFAULT TRUE)

This explain the presence of the signature in rtpostgis_upgrade_cleanup.sql

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.

[strk]

I should add that ST_Value(raster,geometry) is also dropped but no version of PostGIS official ever came out with having that function. Maybe that signature existed in the extra-postgis WKTRaster implementation. It may be useful to replace that one too

[strk]

Fixing this problem is tricky because the Replaces comment handling does not support specifying argument names, so while that comment is ok to handle adding parameters with default values it isn't good in its current form to change parameter names.

[Sandro Santilli <strk@…>]

In 77b75ba/git:

Test upgrade with view using st_value(raster,geometry,bool)

Adds support for argument names in Replaces sql comments.
Includes regression test.

References #5484 in master branch (3.5.0dev)

[strk]

The fix is somewhat dangerous but the automated tests for upgrades from older versions seem to be ok, so I'm not sure whether or not to backport the fix. What is your opinion on the matter Regina ?

[Sandro Santilli <strk@…>]

In fe22c117/git:

Fix upgrade with view using st_value(raster,geometry,bool)

Adds support for argument names in Replaces sql comments.
Includes regression test.

References #5484 in 3.4 branch (3.4.1dev)

[strk]

Well I've backported to 3.4 for now

[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.