script based upgrade from 2.5.6 fails: ERROR: window function st_clusterkmeans requires an OVER clause

[strk]

Script based upgrades from 2.5.6 to 3.3.0dev fail with:

sql:/home/src/postgis/postgis/regress/../regress/00-regress-install/share/contrib/postgis/postgis_upgrade.sql:9033: ERROR:  window function st_clusterkmeans requires an OVER clause
LINE 1:  SELECT st_clusterkmeans($1,$2) 
                ^
QUERY:   SELECT st_clusterkmeans($1,$2) 
CONTEXT:  SQL statement "CREATE OR REPLACE FUNCTION public.st_clusterkmeans_deprecated_by_postgis_302(geom geometry, k integer)
 RETURNS integer
 LANGUAGE sql
 WINDOW STRICT
AS $$ SELECT st_clusterkmeans($1,$2) $$"

It must be the code handling Replaces comments

[strk]

Can be reproduced with this:

regress/run_test.pl --upgrade --upgrade-path unpackaged3.1--:auto regress/core/cluster  -v

Note that upgrades from 3.2 are fine

[strk]

It's incredible that extension upgrade works fine. There must be something wrong I did with handling Replaces which only works in extension and not in script based. I plan to add script-based upgrade testing in Dronie (started with [64abce15057e9606f3d8399ea22dc31a865a52e6/git] and continuing in ​https://git.osgeo.org/gitea/postgis/postgis/pulls/88

[strk]

I'm afraid the fact that non-script based works is hiding a bug somewhere

[strk]

Enabling warnings in the upgrade script shows that ALTER EXTENSION UPDATE does enter the block attempting to rewrite the function in SQL but for some reason that block does not end up raising an exception:

WARNING:  Handling deprecated function st_clusterkmeans_deprecated_by_postgis_302(geometry,integer)
WARNING:  SQL: CREATE OR REPLACE FUNCTION public.st_clusterkmeans_deprecated_by_postgis_302(geom geometry, k integer)
 RETURNS integer
 LANGUAGE sql
 WINDOW STRICT
AS $$ SELECT st_clusterkmeans($1,$2) $$

If I try the command manually I do get the exception. There must be some special environment during extension upgrade/creation that prevents the exception from being thrown.

[strk]

When upgraded via EXTENSION mechanism, the wrapper function GETS created but is non-functioning at runtime:

postgis_reg=# select st_clusterkmeans_deprecated_by_postgis_302(g1, 1) over () from upgrade_test;
ERROR:  window function st_clusterkmeans requires an OVER clause
LINE 1:  SELECT st_clusterkmeans($1,$2)
                ^
QUERY:   SELECT st_clusterkmeans($1,$2)
CONTEXT:  SQL function "st_clusterkmeans_deprecated_by_postgis_302" during startup

[strk]

I'm thinking maybe we should not even try to rewrite deprecated functions. I can envision so many ways for that to fail… The rewrite was added due to #5033

[strk]

I've finally handled to have CI show this error: ​https://dronie.osgeo.org/postgis/postgis/2784/1/3

Next I'm going to fix it and secure it with CI keeping an eye on it

[Sandro Santilli <strk@…>]

In 8cc96d6/git:

Handle exception upon attempts to rewrite deprecated functions

Closes #5080

Also rewrite deprecated as wrappers only after trying to drop them

[Sandro Santilli <strk@…>]

In d59be35/git:

Add test for views using ST_ClusterKMeans on upgrade

References #5080