Thread: 7.2.3: tuple is too big (max 8136)

7.2.3: tuple is too big (max 8136)

From
pilsl@goldfisch.at
Date:
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

Re: 7.2.3: tuple is too big (max 8136)

From
Doug McNaught
Date:
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

Re: 7.2.3: tuple is too big (max 8136)

From
pilsl@goldfisch.at
Date:
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

Re: 7.2.3: tuple is too big (max 8136)

From
Doug McNaught
Date:
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

Re: 7.2.3: tuple is too big (max 8136)

From
Stephan Szabo
Date:
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).


Re: 7.2.3: tuple is too big (max 8136)

From
Tom Lane
Date:
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