Thread: ERROR: row is too big: size 8176, maximum size 8160
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 How is that possible in version 8+? I`ve found some old version 7 manuals that suggest editing /src/include/config.h file but I did not find one for my sources. How could I overcome this problem? Do I need to recompile the database? Do I need to recreate the database already present? Thank you. SDA
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