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.

The server is running PostgreSQL 11.7.

[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