Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#5080 closed defect (fixed)

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

Reported by: strk Owned by: pramsey
Priority: blocker Milestone: PostGIS 3.3.0
Component: postgis Version: master
Keywords: Cc:

Description

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

Change History (9)

comment:1 by strk, 2 years ago

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

comment:2 by strk, 2 years ago

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

comment:3 by strk, 2 years ago

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

comment:4 by strk, 2 years ago

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.

comment:5 by strk, 2 years ago

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

comment:6 by strk, 2 years ago

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

comment:7 by strk, 2 years ago

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

comment:8 by Sandro Santilli <strk@…>, 2 years ago

Resolution: fixed
Status: newclosed

In 8cc96d6/git:

Handle exception upon attempts to rewrite deprecated functions

Closes #5080

Also rewrite deprecated as wrappers only after trying to drop them

comment:9 by Sandro Santilli <strk@…>, 2 years ago

In d59be35/git:

Add test for views using ST_ClusterKMeans on upgrade

References #5080

Note: See TracTickets for help on using tickets.