Re: Maximum number of columns in a table

Thank you gentlemen. I believe I am now enlightened.

1. The maximum number of rows per table is not limited by an algorithm at
CREATE/ALTER TABLE other than to enforce an absolute maximum number of
columns of 1600.

2. The ability to store any individual row depends on its content.

I hasten to add that I am simply catering for a boundary condition which
would (surely) never occur but I have to be defensive here. My aim is simply
to cover this without unduly impacting the normal case.

I have been researching this for some time and I am truly grateful for your
expert and thoughtful assistance.

Gerry

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Sam Mason" <sam@samason.me.uk>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, April 12, 2009 9:26 AM
Subject: Re: [GENERAL] Maximum number of columns in a table


> Sam Mason <sam@samason.me.uk> writes:
>> Just out of interest; what would happen if you had:
>
>>   CREATE TABLE wider (
>>     col0001 TEXT,
>>     col0002 TEXT,
>>     -- [ .... ]
>>     col9998 TEXT,
>>     col9999 TEXT
>>   );
>
>>   CREATE TABLE store (
>>     pk1 INTEGER,
>>     pk2 TEXT,
>>       PRIMARY KEY (pk1,pk2),
>>     data wider
>>   );
>
>> Would the "data" tend to end up toasted, or would PG try and expand the
>> data inline and fail some of the time?
>
> The toast code doesn't recurse into composite values.  It would see the
> "data" column as one single value, so most of the time data would get
> toasted and pushed out as a unit.  You probably don't want to adopt the
> above design.  (Also, you'd still be subject to the 1600 column limit
> on the number of fields within "data", because that comes from a tuple
> header field width limit that has nothing to do with total tuple size.)
>
>> Also, if I ran the following query:
>
>>   SELECT pk1, pk2, (data).col0001, (data).col0101 FROM store;
>
>> Would "data" get detoasted once per row, or per column referenced?
>
> Probably the latter.  I did some work a few months ago trying to make
> the former happen, but it crashed and burned for reasons I don't recall
> at the moment.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general