Opened 11 months ago
Closed 9 months ago
#5545 closed defect (worksforme)
Postgis upgrade from 3.2.3 to 3.3.2 failing on create or update function st_orderingequals
Reported by: | robe | Owned by: | strk |
---|---|---|---|
Priority: | blocker | Milestone: | PostGIS 3.3.5 |
Component: | upgrade | Version: | 3.4.x |
Keywords: | pgextwlist | Cc: |
Description (last modified by )
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)
Change History (19)
comment:1 by , 11 months ago
Component: | postgis → build/upgrade/install |
---|---|
Owner: | changed from | to
Priority: | medium → blocker |
comment:2 by , 11 months ago
comment:4 by , 11 months ago
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);
comment:5 by , 11 months ago
Description: | modified (diff) |
---|
comment:6 by , 11 months ago
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.
follow-up: 10 comment:7 by , 11 months ago
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
comment:8 by , 11 months ago
Keywords: | 5549 added |
---|
comment:9 by , 11 months ago
Keywords: | pgextwlist added; 5549 removed |
---|
comment:10 by , 11 months ago
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.
comment:11 by , 10 months ago
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.
comment:13 by , 10 months ago
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
comment:14 by , 10 months ago
Component: | build → upgrade |
---|
comment:15 by , 10 months ago
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 ?
comment:17 by , 10 months ago
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.
comment:18 by , 10 months ago
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.
@Dan,
Here is the way to work around it for now.
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.