problem about maximum row size ?

Hi ALL ,

I have a question about maximum row size .
In pgsql FAQ, I can find this description :
...
Maximum size for a row? 1.6TB
...

does it mean 1600 text column in a table and 1G every column ?

but, when I test this case by psqlodbc, I got some error :

1.create table testMaxRowSize(c1 text, c2 text, ......c1599 text, c1600
text)
2.insert into testMaxRowSize values('1','2',......'1599','1600')

3.error occurred:
--ERROR: row is too big: size 12832, maximum size 8136

does it mean maximum row size 8136 ?
and int4 is 4 bytes , int8 is 8 bytes , and char ,varchar , text is 20
bytes , ......?

if it is true , then how can I reach the max maximum size 1.6T in FAQ????

I want to test this case , can you help me ??

Regards, Zhao.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: problem about maximum row size ?

zhaoxin writes:
> I have a question about maximum row size .
> In pgsql FAQ, I can find this description :
> ...
> Maximum size for a row? 1.6TB
> ...

> does it mean 1600 text column in a table and 1G every column ?

> but, when I test this case by psqlodbc, I got some error :

This was posted in a fairly randomly chosen list, but I think it does
expose an error in the FAQ: you can't fit 1600 TOAST pointers into one
row and so the 1.6TB figure is overoptimistic.

Since TOAST pointers are 20 bytes, a reasonable number for the maximum
number of large toasted fields is about 400, which would make the
correct entry for this question 400Gb. This could be improved by using
a non-default block size, so this should be listed as one of the limits
affected by block size.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: problem about maximum row size ?

Tom Lane wrote:
> zhaoxin writes:
> > I have a question about maximum row size .
> > In pgsql FAQ, I can find this description :
> > ...
> > Maximum size for a row? 1.6TB
> > ...
>
> > does it mean 1600 text column in a table and 1G every column ?
>
> > but, when I test this case by psqlodbc, I got some error :
>
> This was posted in a fairly randomly chosen list, but I think it does
> expose an error in the FAQ: you can't fit 1600 TOAST pointers into one
> row and so the 1.6TB figure is overoptimistic.
>
> Since TOAST pointers are 20 bytes, a reasonable number for the maximum
> number of large toasted fields is about 400, which would make the
> correct entry for this question 400Gb. This could be improved by using
> a non-default block size, so this should be listed as one of the limits
> affected by block size.

FAQ updated with new number, and mention that increasing block size
quadruples it.

--
Bruce Momjian http://candle.pha.pa.us

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: problem about maximum row size ?

=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Bruce Momjian
> Sent: 03 April 2006 04:41
> To: Tom Lane
> Cc: zhaoxin; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] problem about maximum row size ?
>=20
> FAQ updated with new number, and mention that increasing=20
> block size quadruples it.

I've updated the limitations page on the website, though I didn't bother
with the blocksize hack on there.

Whilst we're on the subject, is 16TB for a table still correct given CE
partitioning?

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: problem about maximum row size ?

Dave Page wrote:
>
>
> > -----Original Message-----
> > From: pgsql-odbc-owner@postgresql.org
> > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Bruce Momjian
> > Sent: 03 April 2006 04:41
> > To: Tom Lane
> > Cc: zhaoxin; pgsql-odbc@postgresql.org
> > Subject: Re: [ODBC] problem about maximum row size ?
> >
> > FAQ updated with new number, and mention that increasing
> > block size quadruples it.
>
> I've updated the limitations page on the website, though I didn't bother
> with the blocksize hack on there.
>
> Whilst we're on the subject, is 16TB for a table still correct given CE
> partitioning?

Uh, probably not, but do we want to require CE to increase that limit?

--
Bruce Momjian http://candle.pha.pa.us

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: problem about maximum row size ?

=20

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]=20
> Sent: 03 April 2006 14:41
> To: Dave Page
> Cc: Tom Lane; zhaoxin; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] problem about maximum row size ?
>=20
> Dave Page wrote:
> >
> >=20
> > > -----Original Message-----
> > > From: pgsql-odbc-owner@postgresql.org=20
> > > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of=20
> Bruce Momjian
> > > Sent: 03 April 2006 04:41
> > > To: Tom Lane
> > > Cc: zhaoxin; pgsql-odbc@postgresql.org
> > > Subject: Re: [ODBC] problem about maximum row size ?
> > >=20
> > > FAQ updated with new number, and mention that increasing=20
> block size=20
> > > quadruples it.
> >=20
> > I've updated the limitations page on the website, though I didn't=20
> > bother with the blocksize hack on there.
> >=20
> > Whilst we're on the subject, is 16TB for a table still=20
> correct given=20
> > CE partitioning?
>=20
> Uh, probably not, but do we want to require CE to increase that limit?

It's worth a mention don't you think? Something like:

Maximum table size: 16TB (for a partitioned table, this is the maximum
size of each partition).

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: problem about maximum row size ?

Dave Page wrote:
> > > > FAQ updated with new number, and mention that increasing
> > block size
> > > > quadruples it.
> > >
> > > I've updated the limitations page on the website, though I didn't
> > > bother with the blocksize hack on there.
> > >
> > > Whilst we're on the subject, is 16TB for a table still
> > correct given
> > > CE partitioning?
> >
> > Uh, probably not, but do we want to require CE to increase that limit?
>
> It's worth a mention don't you think? Something like:
>
> Maximum table size: 16TB (for a partitioned table, this is the maximum
> size of each partition).

OK, FAQ updated by adding second setence:

The maximum table size, row size, and maximum number of columns
can be quadrupled by increasing the default block size to 32k. The
maximum table size can also be increased using table partitioning.