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