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

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