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

The following bug has been logged on the website:

Bug reference: 7756
Logged by: Regina Obe
Email address: lr(at)pcorp(dot)us
PostgreSQL version: 9.2.2
Operating system: Windows 64-bit, Window 32-bit
Description:

This might be a postgis issue, but I don't know how to debug it since I
don't know where the error is coming from (what table it's complaining about
etc.) and no further detail is given in the logs.

I'm the maintainer of the PostGIS extensions so will be happy to change if
I'm doing something wrong in packaging.

PostGIS to upgrade from 2.+ to 2.1 and any interim revision of 2.1 only has
one script which are copied to various versions to support the version
naming of extensions since this creates too much junk I have a hack
extension script to allow me to upgrade PostGIS 2.1 to any 2.1 super r.
Which basically yoyos between 2.1.0 and 2.1.0next and back to 2.1.0

I can upgrade for about 3 times before getting the error and its pretty
consistent. 3 times for the postgis one (postgis_topology one I can go on
for infinity it seems with the same exercise). I'm pretty sure it's
something in our raster machinery tripping it up since if I cut that out,
the extension upgrade can be done indefinitely. Of course our raster
machinery is the piece that is the most volatile and complicated in
structures.

I have the bug more described on this ticket:
http://trac.osgeo.org/postgis/ticket/1959

I've tried on 9.1 and older 9.2 installs and recently upgrade to 9.2.2 with
same issue.

Hi,

On 2012-12-17 13:56:13 +0000, lr(at)pcorp(dot)us wrote:
> row is too big: size 9280, maximum size 8160

If you enable more verbose error output I think you should get a bit
more context, including the table name on which this happens, that might
already help quite a bit. Or do you have that already?
SET log_error_verbosity = 'verbose';

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres,
Sorry not sure why I didn't think of that.

The more descriptive error it gives in logs is:

2012-12-17 09:15:10 EST LOG: statement:
SET log_error_verbosity = 'verbose';
2012-12-17 09:15:13 EST LOG: 00000: statement: ALTER EXTENSION postgis
UPDATE TO "2.1.0SVN";
2012-12-17 09:15:13 EST LOCATION: exec_simple_query,
src\backend\tcop\postgres.c:888
2012-12-17 09:15:15 EST ERROR: 54000: row is too big: size 9272, maximum
size 8160
2012-12-17 09:15:15 EST LOCATION: RelationGetBufferForTuple,
src\backend\access\heap\hio.c:241

This is running 9.2.2 64-bit on windows 2008.

I'll look at that section to see what it could be tripping it up. But if
anyone has any ideas if this is a real bug or a "Regina is doing something
stupid" problem, that would be helpful too.

Thanks,
Regina

-----Original Message-----
From: Andres Freund [mailto:andres(at)2ndquadrant(dot)com]
Sent: Monday, December 17, 2012 9:05 AM
To: lr(at)pcorp(dot)us
Cc: 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

Hi,

On 2012-12-17 13:56:13 +0000, lr(at)pcorp(dot)us wrote:
> row is too big: size 9280, maximum size 8160

If you enable more verbose error output I think you should get a bit more
context, including the table name on which this happens, that might already
help quite a bit. Or do you have that already?
SET log_error_verbosity = 'verbose';

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi,

On 2012-12-17 10:06:53 -0500, Paragon Corporation wrote:
> Andres,
> Sorry not sure why I didn't think of that.
>
> The more descriptive error it gives in logs is:
>
> 2012-12-17 09:15:10 EST LOG: statement:
> SET log_error_verbosity = 'verbose';
> 2012-12-17 09:15:13 EST LOG: 00000: statement: ALTER EXTENSION postgis
> UPDATE TO "2.1.0SVN";
> 2012-12-17 09:15:13 EST LOCATION: exec_simple_query,
> src\backend\tcop\postgres.c:888
> 2012-12-17 09:15:15 EST ERROR: 54000: row is too big: size 9272, maximum
> size 8160
> 2012-12-17 09:15:15 EST LOCATION: RelationGetBufferForTuple,
> src\backend\access\heap\hio.c:241
>
> This is running 9.2.2 64-bit on windows 2008.
>
> I'll look at that section to see what it could be tripping it up. But if
> anyone has any ideas if this is a real bug or a "Regina is doing something
> stupid" problem, that would be helpful too.

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...

# SELECT pg_backend_pid();
> 3493

$ gdb -p 3493
(gdb) b hio.c:24
(gdb) c
....

# ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";
(gdb) bt full

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

I'll try to do that later. This is on my EDB install on server with no dev
tools.

As I recall I think my mingw64 exhibits same issue so I'll do a gdb against
both my edb and mingw using the mingw gdb. Can't get to that until later
unfortunately.

Thanks,
Regina

-----Original Message-----
From: Andres Freund [mailto:andres(at)2ndquadrant(dot)com]
Sent: Monday, December 17, 2012 10:11 AM
To: Paragon Corporation
Cc: 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

Hi,

On 2012-12-17 10:06:53 -0500, Paragon Corporation wrote:
> Andres,
> Sorry not sure why I didn't think of that.
>
> The more descriptive error it gives in logs is:
>
> 2012-12-17 09:15:10 EST LOG: statement:
> SET log_error_verbosity = 'verbose';
> 2012-12-17 09:15:13 EST LOG: 00000: statement: ALTER EXTENSION postgis
> UPDATE TO "2.1.0SVN";
> 2012-12-17 09:15:13 EST LOCATION: exec_simple_query,
> src\backend\tcop\postgres.c:888
> 2012-12-17 09:15:15 EST ERROR: 54000: row is too big: size 9272,
> maximum size 8160
> 2012-12-17 09:15:15 EST LOCATION: RelationGetBufferForTuple,
> src\backend\access\heap\hio.c:241
>
> This is running 9.2.2 64-bit on windows 2008.
>
> I'll look at that section to see what it could be tripping it up. But
> if anyone has any ideas if this is a real bug or a "Regina is doing
> something stupid" problem, that would be helpful too.

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...

# SELECT pg_backend_pid();
> 3493

$ gdb -p 3493
(gdb) b hio.c:24
(gdb) c
....

# ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";
(gdb) bt full

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

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

"Paragon Corporation" <lr(at)pcorp(dot)us> writes:
> 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 :)

For those of us who don't have the latest postgis and all its
dependencies installed, could you extract just the steps of the install
and upgrade scripts that reference this configuration table?

I can see how we might get to this if you were dropping and re-adding
the config table in each cycle, but perhaps that's not what you're doing.

regards, tom lane

On 2012-12-17 12:40:30 -0500, Paragon Corporation wrote:
> 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

Its irritating that the error is happening in that simple_heap_update
where the backtrace indicates the old tuple has a t_len = 7011 and the
only variable length field changed is extversion which shouldn't be that
long.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

I'm beginning to fill a bit dumb.

Attached is a fake_postgis extension that you can just copy the files to
your extension folder that can produce the error if you run the below:

CREATE EXTENSION fake_postgis;
ALTER EXTENSION fake_postgis UPDATE TO "2.1.0next";
ALTER EXTENSION fake_postgis UPDATE TO "2.1.0";
ALTER EXTENSION fake_postgis UPDATE TO "2.1.0next";

I assumed that the :

pg_catalog.pg_extension_config_dump

Calls would overwrite each subsequent for a given object for a given
extension. So I have that in my upgrade script as well should we add more
spatial_ref_sys records we want to avoid dumping.

It seems it just adds.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, December 17, 2012 12:58 PM
To: Paragon Corporation
Cc: 'Andres Freund'; 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

"Paragon Corporation" <lr(at)pcorp(dot)us> writes:
> 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 :)

For those of us who don't have the latest postgis and all its dependencies
installed, could you extract just the steps of the install and upgrade
scripts that reference this configuration table?

I can see how we might get to this if you were dropping and re-adding the
config table in each cycle, but perhaps that's not what you're doing.

regards, tom lane

"Paragon Corporation" <lr(at)pcorp(dot)us> writes:
> Attached is a fake_postgis extension that you can just copy the files to
> your extension folder that can produce the error if you run the below:

Great, thanks for the test case.

> I assumed that the :
> pg_catalog.pg_extension_config_dump
> Calls would overwrite each subsequent for a given object for a given
> extension. So I have that in my upgrade script as well should we add more
> spatial_ref_sys records we want to avoid dumping.
> It seems it just adds.

It probably should overwrite --- this is something we simply didn't
consider in the original coding.

The other case I was considering is that ALTER EXTENSION DROP should
probably remove any extconfig entry for a table that you disassociate
from the extension.

regards, tom lane

I wrote:
> "Paragon Corporation" <lr(at)pcorp(dot)us> writes:
>> I assumed that the :
>> pg_catalog.pg_extension_config_dump
>> Calls would overwrite each subsequent for a given object for a given
>> extension. So I have that in my upgrade script as well should we add more
>> spatial_ref_sys records we want to avoid dumping.
>> It seems it just adds.

> It probably should overwrite --- this is something we simply didn't
> consider in the original coding.

> The other case I was considering is that ALTER EXTENSION DROP should
> probably remove any extconfig entry for a table that you disassociate
> from the extension.

I've committed patches to do the above in 9.1.8 and later.

However, since you'd probably like to update postgis before those
versions are universally installed, what I suggest as a workaround is to
have the extension update scripts do

UPDATE pg_extension SET extconfig = null, extcondition = null
WHERE extname = 'postgis';

before calling pg_extension_config_dump. Obviously, this wipes all your
config-dump data, so if you've got more than one configuration table
you'll need to be sure to do pg_extension_config_dump for each of them
in the update script.

BTW, I thought a bit about adding a TOAST table to pg_extension to
eliminate the limit on the size of extcondition, but didn't actually
do it. We could not make that happen in 9.1 or 9.2, so you'd have to
deal with the limit in any case. Furthermore, the coding technique
you've got here seems like a bad idea anyway. The way I'd suggest doing
it is to add a flag column to spatial_ref_sys so that the dump filter
condition can be simply "WHERE NOT standard_entry" or some such. That
way the labeling can be directly associated with your source data and
there's a lot less chance of failing to update the filter condition.

regards, tom lane

>> "Paragon Corporation" <lr(at)pcorp(dot)us> writes:
>>> I assumed that the :
>>> pg_catalog.pg_extension_config_dump
>>> Calls would overwrite each subsequent for a given object for a given
>>> extension. So I have that in my upgrade script as well should we add
>> more spatial_ref_sys records we want to avoid dumping.
>>> It seems it just adds.

>> It probably should overwrite --- this is something we simply didn't
>> consider in the original coding.

>> The other case I was considering is that ALTER EXTENSION DROP should
>> probably remove any extconfig entry for a table that you disassociate
>> from the extension.

> I've committed patches to do the above in 9.1.8 and later.

> However, since you'd probably like to update postgis before those versions
are universally installed, what I suggest as a workaround is to have the
extension update scripts do

> UPDATE pg_extension SET extconfig = null, extcondition = null WHERE
extname = 'postgis';
> before calling pg_extension_config_dump. Obviously, this wipes all your
config-dump data, so if you've got more than one configuration table you'll
need to be sure to do
> pg_extension_config_dump for each of them in the update script.

Thanks Tom will do.

> BTW, I thought a bit about adding a TOAST table to pg_extension to
eliminate the limit on the size of extcondition, but didn't actually do it.

> We could not make that happen in 9.1 or 9.2, so you'd have to deal with
the limit in any case. Furthermore, the coding technique you've got here
seems like a bad idea anyway.

Agree -- it was meant to be a stopgap and don't really see a big need for
making pg_extension TOAST unless you plan to hold some sort of extension
revision history in those arrays
Which was what I thought might have been the original intention and reason
for not overwritting.

> The way I'd suggest doing it is to add a flag column to spatial_ref_sys
so that the dump filter condition can be simply "WHERE NOT standard_entry"
or some such.
> That way the labeling can be directly associated with your source data and
there's a lot less chance of failing to update the filter condition.

That is our long term plan we just had some concerns about how to make the
upgrade manageable and had a couple with one being the one you mentioned.
All logged in this ticket

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

So the extension config solution I have only works for extensions and just a
stop gap until we settle down on a more permanent solution.

Thanks for all your help,
Regina