Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160

Tom,

I think you may have found the problem.

The postgis extension has a big custom WHERE condition used to exclude the
range of spatial_ref_sys records we package postgis from being backed up.

pg_extension seems to hold that value in the extcondition column and for
each upgrade I do adds another array entry. Though that might be by design
to keep track of previous versions and maybe the designers weren't expecting
someone crazy enough to stuff in a largish where condition :)

So here are the steps to reproduce:

CREATE EXTENSION postgis;

select array_upper(extcondition,1) from pg_extension where extname =
'postgis';

-- returns 1

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";

select array_upper(extcondition,1) from pg_extension where extname =
'postgis';

-- returns 2

ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";

select array_upper(extcondition,1) from pg_extension where extname =
'postgis';

-- returns 3

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";

ERROR: row is too big: size 9272, maximum size 8160

Andres,

I couldn't get my EDB install to give anything meaningful to back trace, but
same issue happens on my mingw dev install (which is running 9.2.1)
I have the backtrace for that on this ticket:

http://trac.osgeo.org/postgis/ticket/1959

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, December 17, 2012 11:25 AM
To: Andres Freund
Cc: Paragon Corporation; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #7756: When upgrading postgis extension get row is
too big: size 9272, maximum size 8160

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2012-12-17 10:06:53 -0500, Paragon Corporation wrote:
>> 2012-12-17 09:15:15 EST ERROR: 54000: row is too big: size 9272,
>> maximum size 8160

> Unfortunately that doesn't tell us very much. Could you get a
> backtrace for that? I don't really see which table should receive such
> large tuples...

Hm ... pg_extension does not have a TOAST table. Could the extconfig and
extcondition fields be getting bloated unreasonably? If I understand the
scenario here, this would require (1) the extension contains a configuration
table (probably one with a filter condition) and (2) for some reason the
repeated updates are adding, not replacing, entries for the table in these
columns.

If that's the story it would be easy to verify by watching the extension's
pg_extension entry as you repeatedly upgrade it.

regards, tom lane