Re: [ADMIN] postgres block_size problem
Bhujbal, Santosh wrote: Hi All, We were getting the following error on executing a COPY command on postgres8.2.0. SQL execution failed, Internal error. SQL Statement: (COPY command failed with error: ERROR: row is too big: size 8200, maximum size 8136) So, in order to fix the error we tried increasing the block size to 16k and recompiled the postgres library. The problem at hand was resolved with the new postgres library and we were now able to populate data for the mentioned query successfully. However, this experiment fails when we tried to run the new postgres on data directory generated with 8k block size which is likely to occur during an upgrade with postgres log showing the following error: 2008-12-30 14:57:33 IST DETAIL: The database cluster was initialized with BLCKSZ 8192, but the server was compiled with BLCKSZ 16384. 2008-12-30 14:57:33 IST HINT: It looks like you need to recompile or initdb. What could be the possible solution to this? If you want to migrate data from an 8k block size data directory to a 16k block size data directory, you need to do a dump and restore, just like on a major version upgrade. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres block_size problem
On Tue, Dec 30, 2008 at 5:28 AM, Bhujbal, Santosh sbhuj...@starentnetworks.com wrote: Hi All, We were getting the following error on executing a COPY command on postgres8.2.0. First and foremost, you need to run the latest version of 8.2, not 8.2.0. .0 releases of postgresql tend to have the most bugs. You're missing two years of updates by running 8.2.0. Who knows what bugs you may be running into by running 8.2.0 While you're at it, you might want to look at upgrading to 8.3.5, which has quite a few enhancements over 8.2.x But it's not that big of a deal. 8.2 is a good performer overall. SQL execution failed, Internal error. SQL Statement: (COPY command failed with error: ERROR: row is too big: size 8200, maximum size 8136) Hmmm. What exactly are you trying to insert into what? Normally when you see something like this it's an index on a text field that causes this type of problem. But I'm not sure what's going on here. Normally postgresql will move large things out of line into a toast table. Are you running a non-standard storage parameter? So, in order to fix the error we tried increasing the block size to 16k and recompiled the postgres library. If you can get away with NOT using 8k blocks, do so. The code base is no where near as well tested with block sizes over 8k as it is with 8k blocks, the default. The problem at hand was resolved with the new postgres library and we were now able to populate data for the mentioned query successfully. However, this experiment fails when we tried to run the new postgres on data directory generated with 8k block size which is likely to occur during an upgrade with postgres log showing the following error: Yeah, running a non-standard block size is only advised if you're willing to go to the extra effort each time to build a new package by hand. And require that of any users who use your application to do the same, or run a custom package you provide. What could be the possible solution to this? Can you explain in more detail exactly what you're doing to cause the problem? A short test case you can post would be most helpful. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres block_size problem
Scott Marlowe scott.marl...@gmail.com writes: On Tue, Dec 30, 2008 at 5:28 AM, Bhujbal, Santosh sbhuj...@starentnetworks.com wrote: SQL execution failed, Internal error. SQL Statement: (COPY command failed with error: ERROR: row is too big: size 8200, maximum size 8136) Hmmm. What exactly are you trying to insert into what? Normally when you see something like this it's an index on a text field that causes this type of problem. It's not an index because the error message would say so. Evidently it's a row that TOAST is unable to squeeze down to a workable size, which suggests a very large number of columns. I'd suggest rethinking the table schema ... regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres block_size problem
On Tue, Dec 30, 2008 at 12:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Scott Marlowe scott.marl...@gmail.com writes: On Tue, Dec 30, 2008 at 5:28 AM, Bhujbal, Santosh sbhuj...@starentnetworks.com wrote: SQL execution failed, Internal error. SQL Statement: (COPY command failed with error: ERROR: row is too big: size 8200, maximum size 8136) Hmmm. What exactly are you trying to insert into what? Normally when you see something like this it's an index on a text field that causes this type of problem. It's not an index because the error message would say so. Evidently it's a row that TOAST is unable to squeeze down to a workable size, which suggests a very large number of columns. I'd suggest rethinking the table schema ... Oh yeah, that does sound like wide tables. Yeah, it's likely highly denormalized or something like that. I thought the error message was different, but I haven't seen it in years... :) -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
