Thread: 7.2.3: tuple is too big (max 8136)
I got this error on older versions and was told to
upgrade. Unfortunately I run into the very same error with 7.2.3.
I have a table with 1600 cols (for testing) and insert one row into
it which results in this error:
$ psql -U peter -f /tmp/t
Password:
psql:/tmp/t:1: ERROR: Tuple is too big: size 19232, max size 8136
where /tmp/t is like:
insert into bigtable values
('testtest','testtest','testtest','testtest','testtest','testtest','testtest','testtest','testtest','testtest','testtest'
....and so on
I tried the same with a perl-program but as expected its exactely the same error.
I searched my whole systems for old libraries from a previous version
but I'm quite sure that my upgrade was quite a clean and successful
one...
thnx,
peter
--
mag. peter pilsl
IT-Consulting
tel: +43-699-1-3574035
fax: +43-699-4-3574035
pilsl@goldfisch.at
pilsl@goldfisch.at writes: > I got this error on older versions and was told to > upgrade. Unfortunately I run into the very same error with 7.2.3. > > I have a table with 1600 cols (for testing) and insert one row into > it which results in this error: It's possible you are hitting the maximum column limit rather than the tuple size limit. The FAQ list the max columns in a table as: "250-1600 depending on column types" So try a tuple of the same size but fewer columns and see if that's the issue. > I searched my whole systems for old libraries from a previous version > but I'm quite sure that my upgrade was quite a clean and successful > one... I'm sure you're right but it's always good to double-check. ;) -Doug
On Fri, Nov 01, 2002 at 08:50:54AM -0500, Doug McNaught wrote: > > It's possible you are hitting the maximum column limit rather than the > tuple size limit. The FAQ list the max columns in a table as: > > "250-1600 depending on column types" > I already created the table with 1600 cols of varchar(30) so this should not be the limit. And the errormessage is clearly talking about the tuples size. (which should not have such a limit in 7.2.3). > So try a tuple of the same size but fewer columns and see if that's > the issue. > I created smaller tables (200-300 cols of varchar(1000)) and inserted rows with long text and the error didnt occure. I cant tell if the size of this tuple is then below the 8k-limit cause I dont know how to calculate the size of the tuple from a given insert-command. (The only idea about the tuple-size is when I get the error where the actual tuple-size is printed. There seems to be no relation between the length of the supplied command and the tuple-size reported in the error) To check the problem it would be very helpful to me to learn more about tuple-size. It seem like postgres would use compression, cause a supplied insert-command of length 620000 lead to the same tuplesize of 20200 than a insert-command of length 20000. > > I'm sure you're right but it's always good to double-check. ;) > I did double and triple. thnx, peter -- mag. peter pilsl IT-Consulting tel: +43-699-1-3574035 fax: +43-699-4-3574035 pilsl@goldfisch.at
pilsl@goldfisch.at writes: > On Fri, Nov 01, 2002 at 08:50:54AM -0500, Doug McNaught wrote: > > > > It's possible you are hitting the maximum column limit rather than the > > tuple size limit. The FAQ list the max columns in a table as: > > > > "250-1600 depending on column types" > > > > I already created the table with 1600 cols of varchar(30) so this > should not be the limit. And the errormessage is clearly talking about > the tuples size. (which should not have such a limit in 7.2.3). Well, it does say "depending on column type". Perhaps it is giving a valid error but the wrong message. The 8k tuple-size limit is definitely gone in modern versions of PG (since 7.1 I believe) so I don't see why that error message should appear. -Doug
On Mon, 4 Nov 2002 pilsl@goldfisch.at wrote: > On Fri, Nov 01, 2002 at 08:50:54AM -0500, Doug McNaught wrote: > > > > It's possible you are hitting the maximum column limit rather than the > > tuple size limit. The FAQ list the max columns in a table as: > > > > "250-1600 depending on column types" > > > > I already created the table with 1600 cols of varchar(30) so this > should not be the limit. And the errormessage is clearly talking about > the tuples size. (which should not have such a limit in 7.2.3). Technically there is still a tuple size limit. It's just that many types can be pulled out of the main storage of the table and you get a reference to its value in the main storage rather than the value itself. That's how the 8k limit was circumvented. The tuple size limit is part of why there's a maximum column count, AFAIK the main tuple still needs to fit in one page. The create table may succeed even if it's not possible to actually insert non-null values in every column (for example, make a table with 1400 int8 columns, if you try to insert values into each column, it will fail. If you try to insert say only a few values and alot of nulls, it works).
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Technically there is still a tuple size limit. It's just that many types
> can be pulled out of the main storage of the table and you get a reference
> to its value in the main storage rather than the value itself. That's how
> the 8k limit was circumvented. The tuple size limit is part of why
> there's a maximum column count, AFAIK the main tuple still needs to fit in
> one page.
Right. You could fit 1600 int4 columns (6400 bytes total), but not 1600
int8 columns. TOAST pointer entries are 20 bytes each, so if all your
columns are toastable types (like varchar) then the upper limit would be
about 400 columns.
In practice, performance will probably suck with more than a couple
hundred columns anyway. You'd be better off rethinking your database
design.
regards, tom lane