Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: mention limitation of the number of resultset columns

  1. PostgreSQL Limits: maximum number of columns in SELECT result

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2022-05-31T07:16:35Z

    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

  2. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    amul sul <sulamul@gmail.com> — 2022-05-31T13:55:53Z

    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

  3. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Dave Cramer <davecramer@postgres.rocks> — 2022-05-31T14:02:48Z

    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

  4. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Dave Cramer <davecramer@postgres.rocks> — 2022-05-31T14:10:13Z

    >
>
>
>
> 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

  5. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-05-31T14:16:45Z

    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

  6. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Dave Cramer <davecramer@postgres.rocks> — 2022-05-31T14:27:16Z

    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.

  7. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-05-31T14:49:44Z

    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

  8. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2022-05-31T15:59:23Z

    >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

  9. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Dave Cramer <davecramer@postgres.rocks> — 2022-05-31T17:22:44Z

    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

  10. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-05-31T18:15:14Z

    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)

  11. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-05-31T18:51:09Z

    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

  12. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Dave Cramer <davecramer@postgres.rocks> — 2022-05-31T19:07:56Z

    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

  13. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Bruce Momjian <bruce@momjian.us> — 2022-06-01T00:16:23Z

    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

  14. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    David Rowley <dgrowleyml@gmail.com> — 2022-06-01T00:32:58Z

    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

  15. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Dave Cramer <davecramer@postgres.rocks> — 2022-06-01T00:37:35Z

    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

  16. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-06-01T00:42:47Z

    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

  17. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Gavin Flower <gavinflower@archidevsys.co.nz> — 2022-06-01T00:50:53Z

    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

  18. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    David Rowley <dgrowleyml@gmail.com> — 2022-06-01T00:51:24Z

    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

  19. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-06-01T00:55:29Z

    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

  20. Re: PostgreSQL Limits: maximum number of columns in SELECT result

    Greg Stark <stark@mit.edu> — 2022-06-02T14:58:33Z

    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