FAIL: upgrade 2.5.0--3.0.0dev: function st_distance(geography, geography) is not unique

[strk]

As reported by our good bot Dronie here: ​https://dronie.osgeo.org/postgis/postgis/184/1/2 (hit "show 250 more lines" on top a few times) our upgrades fail due to:

ERROR: function st_distance(geography, geography) is not unique at character 15

It looks like the upgrade process left some old signature around ? Did anyone add a default ?

A nice view on build history is here: ​https://git.osgeo.org/gitea/postgis/postgis/commits/branch/svn-trunk

From that page the last spot success was on r17294 (although more recent failures may have been due to other reasons)

You can reproduce the failure locally using the utils/check_all_upgrades.sh script (and you could consider updating it to support a switch to make it stop on first failure, for easier debugging)

[strk]

Easier to see build failure (after improving output of CI via r17363) is here: ​https://dronie.osgeo.org/postgis/postgis/186/1/2

[strk]

It looks like the culprit could be r17300, which changed:

-CREATE OR REPLACE FUNCTION _ST_Distance(geography, geography, float8, boolean)
+CREATE OR REPLACE FUNCTION ST_Distance(geog1 geography, geog2 geography, use_spheroid boolean DEFAULT true)

Commit log theoretically moved things into legacy but evidently forgot to add some in drop.sql

[strk]

In the postgis_reg database:

-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------------------
pg_get_functiondef | CREATE OR REPLACE FUNCTION public.st_distance(geography, geography)                                               +
                   |  RETURNS double precision                                                                                         +
                   |  LANGUAGE sql                                                                                                     +
                   |  IMMUTABLE PARALLEL SAFE STRICT                                                                                   +
                   | AS $function$SELECT public._ST_Distance($1, $2, 0.0, true)$function$                                              +
                   | 
-[ RECORD 2 ]------+-------------------------------------------------------------------------------------------------------------------
pg_get_functiondef | CREATE OR REPLACE FUNCTION public.st_distance(geom1 geometry, geom2 geometry)                                     +
                   |  RETURNS double precision                                                                                         +
                   |  LANGUAGE c                                                                                                       +
                   |  IMMUTABLE PARALLEL SAFE STRICT COST 10                                                                           +
                   | AS '$libdir/postgis-3', $function$ST_Distance$function$                                                           +
                   | 
-[ RECORD 3 ]------+-------------------------------------------------------------------------------------------------------------------
pg_get_functiondef | CREATE OR REPLACE FUNCTION public.st_distance(geog1 geography, geog2 geography, use_spheroid boolean DEFAULT true)+
                   |  RETURNS double precision                                                                                         +
                   |  LANGUAGE c                                                                                                       +
                   |  IMMUTABLE PARALLEL SAFE STRICT COST 10                                                                           +
                   | AS '$libdir/postgis-3', $function$geography_distance$function$                                                    +
                   | 
-[ RECORD 4 ]------+-------------------------------------------------------------------------------------------------------------------
pg_get_functiondef | CREATE OR REPLACE FUNCTION public.st_distance(text, text)                                                         +
                   |  RETURNS double precision                                                                                         +
                   |  LANGUAGE sql                                                                                                     +
                   |  IMMUTABLE PARALLEL SAFE STRICT                                                                                   +
                   | AS $function$ SELECT public.ST_Distance($1::public.geometry, $2::public.geometry);  $function$                    +
                   | 

[strk]

In 17364:

Drop deprecated ST_Distance(geography,geography) signature

Fixes #4359