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

DimitryASuplatov <genesup@gmail.com> wrote:

Hello,

I am using postgresql-8.3.7 and have recently got this error:

org.postgresql.util.PSQLException: ERROR: row is too big: size 8168,
maximum size 8160

Please show us your table definition.

Wild guess: you have many, many columns, non-text (INT or something
else), and such a row is bigger than 8 KByte, PG can't use TOAST.

I think, you should re-design your table, read about normalisation.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Andreas Kretschmer wrote:

I am using postgresql-8.3.7 and have recently got this error:

org.postgresql.util.PSQLException: ERROR: row is too big: size 8168,
maximum size 8160

Please show us your table definition.

Wild guess: you have many, many columns, non-text (INT or something
else), and such a row is bigger than 8 KByte, PG can't use TOAST.

I think, you should re-design your table, read about normalisation.

Or maybe a large column has been modified by
ALTER TABLE ... ALTER COLUMN ... SET STORAGE PLAIN

In any case, if you cannot figure out what causes your row to be
so big, tell us the statements used to create the table and give us
an idea of what you insert (the whole row is probably too much spam).

Yours,
Laurenz Albe

On Fri, Mar 5, 2010 at 12:55 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Andreas Kretschmer wrote:

I am using postgresql-8.3.7 and have recently got this error:

org.postgresql.util.PSQLException: ERROR: row is too big: size 8168,
maximum size 8160

Please show us your table definition.

Wild guess: you have many, many columns, non-text (INT or something
else), and such a row is bigger than 8 KByte, PG can't use TOAST.

I think, you should re-design your table, read about normalisation.

Or maybe a large column has been modified by
ALTER TABLE ... ALTER COLUMN ... SET STORAGE PLAIN

In any case, if you cannot figure out what causes your row to be
so big, tell us the statements used to create the table and give us
an idea of what you insert (the whole row is probably too much spam).

I thought even then if the backend can't fit it all in 8k it puts text
out of line.

Scott Marlowe wrote:

I am using postgresql-8.3.7 and have recently got this error:

org.postgresql.util.PSQLException: ERROR: row is too big: size 8168,
maximum size 8160

Please show us your table definition.

Wild guess: you have many, many columns, non-text (INT or something
else), and such a row is bigger than 8 KByte, PG can't use TOAST.

I think, you should re-design your table, read about normalisation.

Or maybe a large column has been modified by
ALTER TABLE ... ALTER COLUMN ... SET STORAGE PLAIN

In any case, if you cannot figure out what causes your row to be
so big, tell us the statements used to create the table and give us
an idea of what you insert (the whole row is probably too

much spam).

I thought even then if the backend can't fit it all in 8k it puts text
out of line.

test=> CREATE TABLE test (id integer PRIMARY KEY, val text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=> ALTER TABLE test ALTER val SET STORAGE PLAIN;
ALTER TABLE
test=> INSERT INTO test VALUES (1, '0123456789');
INSERT 0 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
UPDATE 1
test=> UPDATE test SET val = val || val WHERE id = 1;
ERROR: row is too big: size 10272, maximum size 8164

Yours,
Laurenz Albe

Scott Marlowe <scott.marlowe@gmail.com> writes:

On Fri, Mar 5, 2010 at 12:55 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Or maybe a large column has been modified by
ALTER TABLE ... ALTER COLUMN ... SET STORAGE PLAIN

I thought even then if the backend can't fit it all in 8k it puts text
out of line.

No, SET STORAGE PLAIN is an absolute override, if I recall the code
correctly. That seems an unlikely explanation though. What I'd bet on
is a very large number of non-toastable columns. A thousand or so
float8's would do it for instance. But this is all speculation without
seeing the table declaration.

regards, tom lane