Thread: 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
Attachment
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: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/access/htup_details.h;h=51a60eda088578188b41f4506f6053c2fb77ef0b;hb=HEAD#l23 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:
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
Attachment
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
Attachment
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 65535parameters. 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