Thread: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
lr@pcorp.us
Date:
The following bug has been logged on the website: Bug reference: 7756 Logged by: Regina Obe Email address: lr@pcorp.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.
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Andres Freund
Date:
Hi, On 2012-12-17 13:56:13 +0000, lr@pcorp.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
"Paragon Corporation"
Date:
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@2ndquadrant.com] Sent: Monday, December 17, 2012 9:05 AM To: lr@pcorp.us Cc: pgsql-bugs@postgresql.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@pcorp.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Andres Freund
Date:
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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
"Paragon Corporation"
Date:
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@2ndquadrant.com] Sent: Monday, December 17, 2012 10:11 AM To: Paragon Corporation Cc: pgsql-bugs@postgresql.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Tom Lane
Date:
Andres Freund <andres@2ndquadrant.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
"Paragon Corporation"
Date:
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@sss.pgh.pa.us] Sent: Monday, December 17, 2012 11:25 AM To: Andres Freund Cc: Paragon Corporation; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160 Andres Freund <andres@2ndquadrant.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Tom Lane
Date:
"Paragon Corporation" <lr@pcorp.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Andres Freund
Date:
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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
"Paragon Corporation"
Date:
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@sss.pgh.pa.us] Sent: Monday, December 17, 2012 12:58 PM To: Paragon Corporation Cc: 'Andres Freund'; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160 "Paragon Corporation" <lr@pcorp.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
Attachment
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Tom Lane
Date:
"Paragon Corporation" <lr@pcorp.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
Tom Lane
Date:
I wrote: > "Paragon Corporation" <lr@pcorp.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
Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160
From
"Paragon Corporation"
Date:
>> "Paragon Corporation" <lr@pcorp.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