Postgis upgrade from 3.2.3 to 3.3.2 failing on create or update function st_orderingequals

[robe]

Putting in this ticket for Dan Worthman as he's having issues setting up an osgeo account.

I'm having some issues with upgrading my postgis extension from 3.2.3 to 3.3.2. I'm on postgres engine 14.7 on AWS RDS Aurora, and my failure is coming from running SELECT postgis_extensions_upgrade();

ERROR: cannot change name of input parameter "geometrya"

HINT: Use DROP FUNCTION st_orderingequals(geometry,geometry) first.

I was told this is fixed in unreleased 3.4.1 ​https://trac.osgeo.org/postgis/changeset/e428b70577bd51d3924346d9f0124a6d37ef4ff8/git and I would like to request a backport to the 3.3 branch.

I was also directed to try dropping the function:

postgres⇒ DROP FUNCTION st_orderingequals(geometry,geometry);

ERROR: cannot drop function st_orderingequals(geometry,geometry) because extension postgis requires it

HINT: You can drop extension postgis instead.

Strk told me to try alter extension postgis drop function but I am not permitted to do this in RDS Aurora (I opened a support ticket with AWS to try to solve the problem from this angle)

postgres⇒ alter extension postgis drop function st_orderingequals;

ERROR: must be owner of extension postgis

They also told me that according to git log, commit [6082ff04f3/git] fixed the problem but since I am still facing the issue they recommended I open a ticket.

Here is my search path:

postgres⇒ show search_path;

search_path

---

"$user", public, topology

(1 row)

[robe]

@Dan,

Here is the way to work around it for now.

ALTER FUNCTION st_orderingequals(geometry, geometry) RENAME TO st_orderingequals_old;

That should get you past the upgrade issue. You'll have to address later though.

strk — what is the naming convention you use so it can be auto dropped later.

[strk]

It'd be st_orderingequals_deprecated_by_postgis_300

[strk]

robe I'm not sure if ALTER FUNCTION issued by user (assuming he can, as I suspect he still has permission issues with that) will be enough to get the function dropped later, because of the deprecated function not being unregistered from the extension.

That said, I do see st_orderingequals passed to _postgis_drop_function_if_needed in postgis_before_upgrade.sql of stable-3.3 branch so now I wonder why that failed for the reporter. Maybe the function is not working in some environment ?

For the reporter: what does this query return for you ?

select pg_get_function_identity_arguments('st_orderingequals'::regproc);

[wodan]

Hi there, thank you for the attention on this!

select pg_get_function_identity_arguments('st_orderingequals'::regproc);
   pg_get_function_identity_arguments
----------------------------------------
 geometrya geometry, geometryb geometry
(1 row)

I was able to run ALTER FUNCTION st_orderingequals(geometry, geometry) RENAME TO st_orderingequals_old; but ran into further ownership issues:

postgres=> ALTER FUNCTION st_orderingequals(geometry, geometry) RENAME TO st_orderingequals_old;
ALTER FUNCTION
postgres=> SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis from 3.2.3 to 3.3.2
NOTICE:  Packaging extension postgis_raster
ERROR:  Trying to add VIEW raster_columns to postgis_raster, got must be owner of extension postgis_raster (42501)
CONTEXT:  PL/pgSQL function _postgis_package_object(text,text) line 28 at RAISE
SQL statement "CREATE EXTENSION postgis_raster SCHEMA ecosystem_observation_app VERSION unpackaged;ALTER EXTENSION postgis_raster UPDATE TO "3.3.2""
PL/pgSQL function postgis_extensions_upgrade() line 71 at EXECUTE

I opened a support ticket with AWS who told me that there are remaining raster functions that are no longer supported

unsupported raster functions exist on the instance that are causing the upgrade to fail:

> SELECT postgis_full_version();

	 postgis_full_version                                                                
	--------------------
	 RASTER (raster procs from "2.5.1 r0" need upgrade)

I plan to follow ​https://postgis.net/documentation/tips/tip-removing-raster-from-2-3/, please let me know if that sounds right to you.

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

[wodan]

Hopefully I did not convolute things this way, but postgres=> ALTER EXTENSION postgis UPDATE; was successful, but running SELECT postgis_extensions_upgrade(); resulted in a different error:

postgres=> SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis 3.3.2
NOTICE:  version "3.3.2" of extension "postgis" is already installed
NOTICE:  Packaging and updating postgis_raster
ERROR:  Trying to add VIEW raster_columns to postgis_raster, got must be owner of extension postgis_raster (42501)
CONTEXT:  PL/pgSQL function _postgis_package_object(text,text) line 28 at RAISE
SQL statement "CREATE EXTENSION postgis_raster SCHEMA ecosystem_observation_app VERSION unpackaged;ALTER EXTENSION postgis_raster UPDATE TO "3.3.2""
PL/pgSQL function postgis_extensions_upgrade() line 77 at EXECUTE

I was able to run the CREATE OR REPLACE FUNCTION _postgis_drop_function_if_needed and also ran

postgres=> SELECT _postgis_drop_function_if_needed
        (
        'st_orderingequals_deprecated_by_postgis_300',
        'GeometryA geometry, GeometryB geometry'
        );
 _postgis_drop_function_if_needed
----------------------------------
 
(1 row)

This suggests I have no raster tables:

postgres=> SELECT count(1) FROM raster_columns;
 count 
-------
     0
(1 row)

AWS Suggested I drop the postgis_raster extension, but I get the following:

postgres=> DROP EXTENSION postgis_raster;
ERROR:  extension "postgis_raster" does not exist

postgres=> select * FROM pg_available_extensions where name like '%postgis%';
          name          | default_version | installed_version |                          comment                           
------------------------+-----------------+-------------------+------------------------------------------------------------
 postgis_tiger_geocoder | 3.3.2           | 3.2.3             | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.3.2           | 3.2.3             | PostGIS topology spatial types and functions
 postgis                | 3.3.2           | 3.3.2             | PostGIS geometry and geography spatial types and functions
 postgis_raster         | 3.3.2           |                   | PostGIS raster types and functions
(4 rows)

So it seems like the upgrade was partially successful?

I can recreate the DB from the initial failed select postgis_extensions_upgrade(); if you would like me to test anything.

[strk]

The ALTER EXTENSION postgis statement only affects the "postgis" extension, while the SELECT postgis_extensions_upgrade(); attempts to deal with all extentions being part of the core PostGIS distribution, and also tries to _package_ support functions that are present in the database but not yet part of an extension.

In your case, the _raster_ support functions are found in the database but are not packaged. This was probably the result of updating PostGIS extension from a version older than 3.0.0 which is the version in which raster support was moved out of the "postgis" extension and in its own "postgis_raster" extension.

Now, if you don't need raster, what you want to do is DROP the raster support functions. This is usually easy when SELECT postgis_extensions_upgrade() is able to package those raster functions, as then you just drop the extension, but you stumbled upon a bug which prevents packaging of those functions (now filed as #5550), and is a bug that's most likely only encountered when using a sandboxed system, because we do have regression testing in place and all bots are happy with the repackaging of raster (see #5212 for a ticket to request improving the CI coverage to add this case).

The above said, I'm not sure there's much more to do in this ticket. The main problem was that the upgrade script from 3.2.3 to 3.3.2 failed to drop the st_orderingequals, but we didn't yet confirm it was due to sandboxing. I'm not sure your invocation of

SELECT _postgis_drop_function_if_needed
        (
        'st_orderingequals_deprecated_by_postgis_300',
        'GeometryA geometry, GeometryB geometry'
        );

Did something or not, maybe you can re-run after set client_min_messages to DEBUG; to know more ? What we are interested in is WHY that function wasn't removed when you initially upgraded.

[strk]

CI coverage of sandboxed systems ticket filed: #5566

[strk]

Note that if you're looking for a workaround, uninstalling any raster object from the database could be done via the script uninstaller, which should be, for 3.2, installed in $(pg_config --sharedir)/contrib/postgis-3.2/uninstall_rtpostgis.sql

[strk]

wodan could you please show who's the owner of the raster_columns ? And owner of the "postgis_raster" extension ? Is either one or both superuser ?

[Sandro Santilli <strk@…>]

In e2bf40c/git:

Set ownership of packaged functions to extension owner

Still check that the functions were owned by a superuser.

References #4648
References #5545
References #5566

[strk]

As of [e2bf40c322c8805ca13e5a270b98b2793ee436a3/git] we change ownership of unpackaged function at packaging time, after checking they are owned by a superuser. This may or may not fix this problem, as PostgreSQL core could be preventing packaging earlier in the process. For some reason I don't see failure in pgextwlist-based CI at this stage.

[strk]

Refer to #5550 for the re-packaging of raster issue.

As for the st_orderingequals issue we still weren't able to reproduce it and are lucking more info from the reporter (the questions posed from ​https://trac.osgeo.org/postgis/ticket/5545#comment:11 onward). I would close this ticket as "worksforme" lacking feedback for another week.

[robe]

Yah lets close this.