PostGIS upgrade from 3.2 to 3.3 fails with deprecated function not part of PostGIS error

[robe]

strk suggests this may be an issue, or maybe not.

So I am documenting as best I can remember

This system was running PostGIS 3.0.1 and PostgreSQL 11 on a Windows 2012 (maybe 2016, can't recall, but probably not important) server.

I tried to upgrade it first to PostGIS 3.3.2 as noted in #5301 and got a ERROR: row is too big: size 8824, maximum size 8160 which I think is a separate issue (has to do most likely with how we try to update spatial_ref_sys in 3.3)

So since I couldn't upgrade to 3.3, I decided to do the next best thing to upgrade to 3.2.3.

Then ran the usual

SELECT postgis_extensions_upgrade();

That was all successful. So like a monkey I decided, let me try again to upgrade to 3.3.2. The example NOTICE I received was

NOTICE:  Updating extension postgis from 3.2.3 to 3.3.2
WARNING:  Deprecated function st_intersection_deprecated_by_postgis_301(geometry,geometry) left behind: cannot drop function st_intersection_deprecated_by_postgis_301(geometry,geometry) because other objects depend on it
DETAIL:  function pgis_geom_intersection(geometry) depends on function st_intersection_deprecated_by_postgis_301(geometry,geometry)
view assessing.vwparcel_electdist depends on function st_intersection_deprecated_by_postgis_301(geometry,geometry)
materialized view assessing.vwparceltime_for_pdr depends on function st_intersection_deprecated_by_postgis_301(geometry,geometry)
materialized view sf_staging.mvw_dbamp_parcels depends on function st_intersection_deprecated_by_postgis_301(geometry,geometry)
HINT:  Replace the view changing all occurrences of st_intersection_deprecated_by_postgis_301(geometry,geometry) in its definition with st_intersection and upgrade again
WARNING:  Deprecated function st_intersection_deprecated_by_postgis_301(geometry,geometry) could not be rewritten as a wrapper: function st_intersection_deprecated_by_postgis_301(geometry,geometry) is not a member of extension "postgis" (55000)
ERROR:  row is too big: size 8824, maximum size 8160
CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO "3.3.2";"
PL/pgSQL function postgis_extensions_upgrade() line 82 at EXECUTE
SQL state: 54000

[robe]

Oh one last note I should add. I think the server when I upgraded it to 3.2.3 was running a PostgreSQL 11 without the CVEE patch. After failing again with 3.3.2 upgrade, I had decided to upgrade the server to PostgreSQL 11 to latest version (to rule out the row size thing had anything to do with that). So the above attempt was after I had upgraded the server to PostgreSQL 11.18 (I think before it was running something like 11.13)

[strk]

Can you check if SELECT proname FROM pg_proc WHERE proname like 'deprecated_by_postgis%' yelds any results prior to upgrade ? It feels like you did not cleanup after upgrade to 3.1

[robe]

Yes it did. But wuery needs to be below because the deprecated part is at the end of the name.

SELECT proname FROM pg_proc WHERE proname like '%deprecated_by_postgis%'

I wonder if we should add this to the upgrade instructions.

Note there was a pgis_geom_intersection which relied on st_intersection_deprecated_by_postgis_301. On trying to drop st_intersection_deprecated_by_postgis_301, that failed. So had to drop both

DROP FUNCTION pgis_geom_intersection(geometry);
DROP FUNCTION st_intersection_deprecated_by_postgis_301(geometry,geometry)

Only thing that puzzles me is that helper function pgis_geom_intersection, why it was there in the first place. I couldn't find it listed in any versions of our source code.

It's possible maybe I created it and overwrote the ST_Intersection to use it for some reason a very long time ago.