[postgis-tickets] [PostGIS] #5545: Postgis upgrade from 3.2.3 to 3.3.2 failing on create or update function st_orderingequals

[PostGIS]

#5545: Postgis upgrade from 3.2.3 to 3.3.2 failing on create or update function
st_orderingequals
------------------------------------+---------------------------
  Reporter:  robe                   |      Owner:  strk
      Type:  defect                 |     Status:  new
  Priority:  blocker                |  Milestone:  PostGIS 3.3.5
 Component:  build/upgrade/install  |    Version:  3.4.x
Resolution:                         |   Keywords:
------------------------------------+---------------------------
Comment (by strk):

 The mention of "unpackaged" in postgis_raster creation means that
 `postgis_extensions_upgrade()` found PostGIS Raster being installed as
 unpackaged and is trying to package it. On AWS I think the only way to get
 an unpackaged postgis raster would be by upgrading from PostGIS 2.x to 3.x
 which should have printed a WARNING about this "unpackaging" process.

 The fact that re-packaging fails is worth a separate ticket, which I filed
 as #5550

 Specific to this ticket, instead, is the fact that the upgrade script from
 3.2.3 to 3.3.2 failed to drop the st_orderingequals, which may also be due
 to the sandboxing.

 The function responsible to drop incompatible signatures is
 [https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.3.2/postgis/postgis_before_upgrade.sql#L23-L73
 the following one]:
 {{{
 CREATE OR REPLACE FUNCTION _postgis_drop_function_if_needed(
         function_name text,
         function_arguments text) RETURNS void AS $$
 DECLARE
         sql_drop text;
         postgis_namespace OID;
         matching_function REGPROCEDURE;
 BEGIN

         -- Fetch install namespace for PostGIS
         SELECT n.oid
         FROM pg_catalog.pg_proc p
         JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
         WHERE proname = 'postgis_full_version'
         INTO postgis_namespace;

         -- Find a function matching the given signature
         SELECT oid
         FROM pg_catalog.pg_proc p
         WHERE pronamespace = postgis_namespace
         AND pg_catalog.LOWER(p.proname) = pg_catalog.LOWER(function_name)
         AND pg_catalog.pg_function_is_visible(p.oid)
         AND
 pg_catalog.LOWER(pg_catalog.pg_get_function_identity_arguments(p.oid)) ~
 pg_catalog.LOWER(function_arguments)
         INTO matching_function;

         IF matching_function IS NOT NULL THEN
                 sql_drop := 'DROP FUNCTION ' || matching_function;
                 RAISE DEBUG 'SQL query: %', sql_drop;
                 BEGIN
                         EXECUTE sql_drop;
                 EXCEPTION
                         WHEN OTHERS THEN
                                 RAISE EXCEPTION 'Could not drop function
 %. You might need to drop dependant objects. Postgres error: %',
 function_name, SQLERRM;
                 END;
         END IF;

 END;
 $$ LANGUAGE plpgsql;
 }}}

 The above function is
 [https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.3.2/postgis/postgis_before_upgrade.sql#L178-L184
 called] during upgrade like this:
 {{{
 -- FUNCTION st_orderingequals changed argument names in 3.0
 -- Was (GeometryA geometry, GeometryB geometry) and now (geom1 geometry,
 geom2 geometry)
 SELECT _postgis_drop_function_if_needed
         (
         'st_orderingequals',
         'GeometryA geometry, GeometryB geometry'
         );
 }}}

 Now that you changed the name of the function you could test calling it
 as:
 {{{
 SELECT _postgis_drop_function_if_needed
         (
         'st_orderingequals_old',
         'GeometryA geometry, GeometryB geometry'
         );
 }}}

 to see if at least the function is found. Note that the
 _postgis_drop_function_if_needed function is removed by the end of the
 upgrade so you'll need to re-create that one for testing.

 I guess we'd find lots of these issues by adding a bot to check under
 pgextwlist:
 see #5549
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5545#comment:7>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.