PostgreSQL 11 3.0.1 upgrade to 3.3.2 fails with ERROR: row is too big: size 8824, maximum size 8160

[robe]

I was upgrading a 3.0.1 PG 11 server on windows to 3.3.2

SELECT postgis_extensions_upgrade()

and got this error

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 68 at EXECUTE
SQL state: 54000

This is the first PostGIS server I have ever worked on.

[robe]

I'm suspecting it's the spatial_ref_sys change since upgrading to PostGIS 3.2.3 worked fine. I should add this server has existed since the 1.0 days so the spatial_ref_sys table is probably very out of date.

The server was running PostgreSQL 11.7. We upgraded it to latest PostgreSQL:

It now shows:

POSTGIS="3.3.2 3.3.2" [EXTENSION] PGSQL="110" GEOS="3.11.1-CAPI-1.17.1" PROJ="7.2.1" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" (core procs from "3.2.3 3.2.3" need upgrade) TOPOLOGY (topology procs from "3.2.3 3.2.3" need upgrade) RASTER (raster procs from "3.2.3 3.2.3" need upgrade) (deprecated functions exist, upgrade is not complete) PostgreSQL 11.18, compiled by Visual C++ build 1914, 64-bit

Tried to upgrade again and got same issue. So I guess there is a downside to how we are upgrading spatial_ref_sys. Not sure if there is anything we can do about this.

[robe]

Just a side note this database also couldn't be upgraded because of this:

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" 

I thought when we renamed the deprecated functions, they are still part of postgis, so this part came as a surprise to me, and perhaps left over from our security fix changes.

This issue is ticketed on #5302

[robe]

Okay the good news is this issue shouldn't be very common and I don't think it's the issue I thought it was. I think it's the backup config at fault. Now I remember that's the last time I have seen this issue before.

To try to check if the issue was what I thought it was: To try to replicate I did this:

CREATE EXTENSION postgis; --3.3.2

TRUNCATE TABLE spatial_ref_sys;

-- restored the backup of spatial_ref_sys I was given

SELECT COUNT(*) FROM spatial_ref_sys;
-- output 471 rows

SELECT postgis_extensions_upgrade();   -- worked fine


SELECT count(*) FROM spatial_ref_sys;  -- 8508 rows

So update of spatial_ref_sys entries I suspect is not the culprit here. My next guess is our long call to

SELECT pg_catalog.pg_extension_config_dump('spatial_ref_sys', 'WHERE NOT (...');

There was a time which I thought it was fixed, when I complained to Tom Lane about this that instead of overriting it just kept on appending to it. I forget his comment about it and if he thought it should behave that was or is something that needs fixing or if I was supposed to be doing something different.

So next time I have access to that system I'll check what kind of mess that entry looks like.

[robe]

I did a quick test running pg_extensions_postgis_upgrade() in a DO loop 100 times.

DO $$
    BEGIN
        FOR i IN 1..100 LOOP
            PERFORM postgis_extensions_upgrade();
            COMMIT;
        END LOOP;
    END;
$$;

and I checked:

SELECT extcondition
	FROM pg_catalog.pg_extension where extname = 'postgis';

and it's not appending, so I think that old issue I remembered is long fixed.

I guess I'll just have to wait till I have access to that server again, so confirm that is just filled in with piles of stuff.

It is still a little suspicious it only broke on 3.3.2 upgrade and not any of the others.

[strk]

As a test you may unpackage the extension (extensions_unpackage.sh) and then source postgis_upgrade.sql — if not else you'd get a line number

[robe]

This is fixed. The issue was the pg_extension table. I think it was left over from a time when PostgreSQL was just appending instead of overwriting the configurations

SELECT extcondition, extconfig
	FROM pg_catalog.pg_extension where extname = 'postgis';

Yielded the below

extconfig = 3383360,3383360,3383360
extcondition = ARRAY[ of 3 text values]

I noticed on my fresh system the arrays had only one element, though the extconfig value in it was different, so that changes by system.

To fix I did:

UPDATE pg_extension SET extcondition =  ARRAY[''], extconfig = ARRAY[3383360]  WHERE extname = 'postgis';
SELECT postgis_extensions_upgrade();

I don't think there is a fix on our end for this kind of issue, and no one may ever run into it again. So flipping this to PostgreSQL