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@xxxxxxxxxxxxx>
To: "Sam Mason" <sam@xxxxxxxxxxxxx>
Cc: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Sunday, April 12, 2009 9:26 AM
Subject: Re: Maximum number of columns in a table
Sam Mason <sam@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
