PostgreSQL Limits: maximum number of columns in SELECT result
Hi,
Today I hit "ERROR: target lists can have at most 1664 entries", and I was
surprised the limit was not documented.
I suggest that the limit of "1664 columns per tuple" (or whatever is the
right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html e.g.
after "columns per table".
Could someone please commit that 1-2 line doc improvement or do you need a
patch for it?
Vladimir
On Tue, May 31, 2022 at 12:46 PM Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
Hi,
Today I hit "ERROR: target lists can have at most 1664 entries", and I was surprised the limit was not documented.
I suggest that the limit of "1664 columns per tuple" (or whatever is the right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html e.g. after "columns per table".
Rather, I think the "columns per table" limit needs to be updated to 1664.
Regards,
Amul
On Tue, 31 May 2022 at 09:56, Amul Sul <sulamul@gmail.com> wrote:
On Tue, May 31, 2022 at 12:46 PM Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:Hi,
Today I hit "ERROR: target lists can have at most 1664 entries", and I
was surprised the limit was not documented.
I suggest that the limit of "1664 columns per tuple" (or whatever is the
right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html e.g.
after "columns per table".
Rather, I think the "columns per table" limit needs to be updated to 1664.
Actually that is correct. Columns per table is MaxHeapAttributeNumber which
is 1600.
MaxTupleAttributeNumber is 1664 and is the limit of user columns in a
tuple.
Dave
On Tue, 31 May 2022 at 09:56, Amul Sul <sulamul@gmail.com> wrote:
On Tue, May 31, 2022 at 12:46 PM Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:Hi,
Today I hit "ERROR: target lists can have at most 1664 entries", and I
was surprised the limit was not documented.
I suggest that the limit of "1664 columns per tuple" (or whatever is
the right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html
e.g. after "columns per table".
Rather, I think the "columns per table" limit needs to be updated to 1664.
Actually that is correct. Columns per table is MaxHeapAttributeNumber
which is 1600.MaxTupleAttributeNumber is 1664 and is the limit of user columns in a
tuple.Dave
Attached is a patch to limits.sgml. I'm not sure this is where it belongs,
as it's not a physical limit per-se but I am not familiar enough with the
docs to propose another location.
Note this was suggested by Vladimir.
see attached
Attachments:
columns_per_tuple.patchapplication/octet-stream; name=columns_per_tuple.patchDownload
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index 7713ff7177..1e79c317f3 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -62,6 +62,12 @@
below</entry>
</row>
+ <row>
+ <entry>user columns per tuple</entry>
+ <entry>1664</entry>
+ <entry>Limit of user columns per tuple</entry>
+ </row>
+
<row>
<entry>field size</entry>
<entry>1 GB</entry>
Amul Sul <sulamul@gmail.com> writes:
On Tue, May 31, 2022 at 12:46 PM Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:I suggest that the limit of "1664 columns per tuple" (or whatever is the right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html e.g. after "columns per table".
We've generally felt that the existing "columns per table" limit is
sufficient detail here.
Rather, I think the "columns per table" limit needs to be updated to 1664.
That number is not wrong. See MaxTupleAttributeNumber and
MaxHeapAttributeNumber:
regards, tom lane
On Tue, 31 May 2022 at 10:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Amul Sul <sulamul@gmail.com> writes:
On Tue, May 31, 2022 at 12:46 PM Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:I suggest that the limit of "1664 columns per tuple" (or whatever is
the right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html
e.g. after "columns per table".
We've generally felt that the existing "columns per table" limit is
sufficient detail here.
ISTM that adding detail is free whereas the readers time to figure out why
and where this number came from is not.
I think it deserves mention.
Regards,
Dave.
Dave Cramer <davecramer@postgres.rocks> writes:
On Tue, 31 May 2022 at 10:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We've generally felt that the existing "columns per table" limit is
sufficient detail here.
ISTM that adding detail is free whereas the readers time to figure out why
and where this number came from is not.
Detail is far from "free". Most readers are going to spend more time
wondering what the difference is between "columns per table" and "columns
per tuple", and which limit applies when, than they are going to save by
having the docs present them with two inconsistent numbers.
regards, tom lane
ost readers are going to spend more time
wondering what the difference is between "columns per table" and "columns
per tuple"
"tuple" is already mentioned 10 times on "limits" page, so adding "columns
per tuple" is not really obscure.
The comment could be like "for instance, max number of expressions in each
SELECT clause"
I know I visited current/limits.html many times (mostly for things like
"max field length")
However, I was really surprised there's an easy to hit limit on the number
of expressions in SELECT.
I don't ask to lift the limit, however, I am sure documenting the limit
would make it clear
for the application developers that the limit exists and they should plan
for it in advance.
----
I bumped into "target lists can have at most 1664 entries" when I was
trying to execute a statement with 65535 parameters.
I know wire format uses unsigned int2 for the number of parameters, so I
wanted to test if the driver supports that.
a) My first test was like select ? c1, ? c2, ? c3, ..., ? c65535
Then it failed with "ERROR: target lists can have at most 1664 entries".
I do not think "columns per table" is applicable to select like that
b) Then I tried select ?||?||?||?||....||?
I wanted to verify that the driver sent all the values properly, so I don't
want to just ignore them and I concatenated the values.
Unfortunately, it failed with "stack depth limit exceeded. Increase the
configuration parameter "max_stack_depth" (currently 2048kB), after
ensuring the platform's stack depth limit is adequate"
Finally, I settled on select ARRAY[?, ?, ... ?] which worked up to 65535
parameters just fine.
Please, do not suggest me avoid 65535 parameters. What I wanted was just to
test that the driver was able to handle 65535 parameters.
Vladimir
On Tue, 31 May 2022 at 10:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dave Cramer <davecramer@postgres.rocks> writes:
On Tue, 31 May 2022 at 10:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We've generally felt that the existing "columns per table" limit is
sufficient detail here.ISTM that adding detail is free whereas the readers time to figure out
why
and where this number came from is not.
Detail is far from "free". Most readers are going to spend more time
wondering what the difference is between "columns per table" and "columns
per tuple", and which limit applies when, than they are going to save by
having the docs present them with two inconsistent numbers.
Sounds to me like we are discussing different sides of the same coin. On
one hand we have readers of the documentation who may be confused,
and on the other hand we have developers who run into this and have to
spend time digging into the code to figure out what's what.
For me, while I have some familiarity with the server code it takes me
quite a while to load and find what I am looking for.
Then we have the less than clear names like "resno" for which I still
haven't groked. So imagine someone who has no familiarity
with the backend code trying to figure out why 1664 is relevant when the
docs mention 1600. Surely there must be some middle ground
where we can give them some clues without having to wade through the source
code ?
Dave
On 2022-May-31, Tom Lane wrote:
Detail is far from "free". Most readers are going to spend more time
wondering what the difference is between "columns per table" and "columns
per tuple", and which limit applies when, than they are going to save by
having the docs present them with two inconsistent numbers.
I think it's reasonable to have two adjacent rows in the table for these
two closely related things, but rather than "columns per tuple" I would
label the second one "columns in a result set". This is easy enough to
understand and to differentiate from the other limit.
(Replacing "in a" with "per" sounds OK to me but less natural, not sure
why.)
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
I think it's reasonable to have two adjacent rows in the table for these
two closely related things, but rather than "columns per tuple" I would
label the second one "columns in a result set". This is easy enough to
understand and to differentiate from the other limit.
OK, with that wording it's probably clear enough.
regards, tom lane
On Tue, 31 May 2022 at 14:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
I think it's reasonable to have two adjacent rows in the table for these
two closely related things, but rather than "columns per tuple" I would
label the second one "columns in a result set". This is easy enough to
understand and to differentiate from the other limit.OK, with that wording it's probably clear enough.
regards, tom lane
Reworded patch attached
Attachments:
columns_per_tuple.patchapplication/octet-stream; name=columns_per_tuple.patchDownload
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index 7713ff7177..98bcd80c31 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -62,6 +62,12 @@
below</entry>
</row>
+ <row>
+ <entry>user columns in a resultset</entry>
+ <entry>1664</entry>
+ <entry>Limit of user columns in a resultset</entry>
+ </row>
+
<row>
<entry>field size</entry>
<entry>1 GB</entry>
On Tue, May 31, 2022 at 01:22:44PM -0400, Dave Cramer wrote:
On Tue, 31 May 2022 at 10:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dave Cramer <davecramer@postgres.rocks> writes:
On Tue, 31 May 2022 at 10:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We've generally felt that the existing "columns per table" limit is
sufficient detail here.ISTM that adding detail is free whereas the readers time to figure out
why
and where this number came from is not.
Detail is far from "free". Most readers are going to spend more time
wondering what the difference is between "columns per table" and "columns
per tuple", and which limit applies when, than they are going to save by
having the docs present them with two inconsistent numbers.Sounds to me like we are discussing different sides of the same coin. On one
hand we have readers of the documentation who may be confused,
and on the other hand we have developers who run into this and have to spend
time digging into the code to figure out what's what.
How many people ask about this limit. I can't remember one.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
On Wed, 1 Jun 2022 at 07:08, Dave Cramer <davecramer@postgres.rocks> wrote:
On Tue, 31 May 2022 at 14:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
I think it's reasonable to have two adjacent rows in the table for these
two closely related things, but rather than "columns per tuple" I would
label the second one "columns in a result set". This is easy enough to
understand and to differentiate from the other limit.OK, with that wording it's probably clear enough.
Reworded patch attached
I see the patch does not have the same text as what was proposed and
seconded above. My personal preferences would be "result set
columns", but "columns in a result set" seems fine too.
I've adjusted the patch to use the wording proposed by Alvaro. See attached.
I will push this shortly.
David
Attachments:
document_result_set_columns_limit.patchtext/plain; charset=US-ASCII; name=document_result_set_columns_limit.patchDownload
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index 7713ff7177..d5b2b627dd 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -62,6 +62,12 @@
below</entry>
</row>
+ <row>
+ <entry>columns in a result set</entry>
+ <entry>1664</entry>
+ <entry></entry>
+ </row>
+
<row>
<entry>field size</entry>
<entry>1 GB</entry>
On Tue, 31 May 2022 at 20:33, David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 1 Jun 2022 at 07:08, Dave Cramer <davecramer@postgres.rocks>
wrote:On Tue, 31 May 2022 at 14:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
I think it's reasonable to have two adjacent rows in the table for
these
two closely related things, but rather than "columns per tuple" I
would
label the second one "columns in a result set". This is easy enough
to
understand and to differentiate from the other limit.
OK, with that wording it's probably clear enough.
Reworded patch attached
I see the patch does not have the same text as what was proposed and
seconded above. My personal preferences would be "result set
columns", but "columns in a result set" seems fine too.I've adjusted the patch to use the wording proposed by Alvaro. See
attached.I will push this shortly.
David
Thanks David, Apparently I am truly unable to multi-task.
Dave
David Rowley <dgrowleyml@gmail.com> writes:
I've adjusted the patch to use the wording proposed by Alvaro. See attached.
Should we also change the adjacent item to "columns in a table",
for consistency of wording? Not sure though, because s/per/in a/
throughout the list doesn't seem like it'd be an improvement.
regards, tom lane
On 1/06/22 12:42, Tom Lane wrote:
David Rowley <dgrowleyml@gmail.com> writes:
I've adjusted the patch to use the wording proposed by Alvaro. See attached.
Should we also change the adjacent item to "columns in a table",
for consistency of wording? Not sure though, because s/per/in a/
throughout the list doesn't seem like it'd be an improvement.regards, tom lane
I like the word 'per' better than the phrase 'in a', at least in this
context.
(Though I'm not too worried either way!)
Cheers,
Gavin
On Wed, 1 Jun 2022 at 12:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
I've adjusted the patch to use the wording proposed by Alvaro. See attached.
Should we also change the adjacent item to "columns in a table",
for consistency of wording? Not sure though, because s/per/in a/
throughout the list doesn't seem like it'd be an improvement.
I might agree if there weren't so many other "per"s in the list.
Maybe "columns per result set" would have been a better title for consistency.
David
David Rowley <dgrowleyml@gmail.com> writes:
Maybe "columns per result set" would have been a better title for consistency.
I can't quite put my finger on why, but that wording seems odd to me,
even though "columns per table" is natural enough. "In a" reads much
better here IMO. Anyway, I see you committed it that way, and it's
certainly not worth the effort to change further.
regards, tom lane
On Tue, 31 May 2022 at 12:00, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
Please, do not suggest me avoid 65535 parameters. What I wanted was just to test that the driver was able to handle 65535 parameters.
I don't think we have regression tests to cover things at these
limits, that might be worth adding if they're not too awkward to
maintain.
--
greg