Re: "memory exhausted" in query parser/simplifier for many nested parentheses

On Fri, Dec 13, 2024 at 8:53 AM Niklas Hambüchen <mail(at)nh2(dot)me> wrote:

> As a user, how can I know that 10000 list entries in "x in (A, B, ...)"
> will not also hit an arbitrary parser implementation detail hardcoded limit?
> How shall the user have confidence that the parser is better at handling
> multiple commas than multiple parens?
>

As an application developer, you test it, especially if your application
has the potential to generate insanely large queries. For the record, the
catch point on my system using Postgres 17 seems to be around 8.4 *MILLION*
items for IN() lists:

greg=# \set VERBOSITY terse
greg=# do $$begin execute format('SELECT 1 WHERE 1 IN (%s1)',
repeat('123,',8_385_000));end$$;
DO
greg=# do $$begin execute format('SELECT 1 WHERE 1 IN (%s1)',
repeat('123,',8_390_000));end$$;
ERROR: invalid memory alloc request size 1073741824

> None of that seems to be documented anywhere

Documentation patches are always welcome. Perhaps at
https://www.postgresql.org/docs/current/limits.html ?

In absence of such docs, one must build systems that later fail at
> arbitrary limits when e.g. the user clicks some larger number of checkboxes
> that construct a batch query.
>

That's a bit of a strawman argument: queries this large are not going to be
caused by checkboxes being clicked.

If I build some workaround today, e.g. splitting the query into multiple
> ones of max length N, how do I know it will still work in the future, e.g.
> if Postgres changes the Bison version or switches to a different parser?
>

You don't, that's the nature of software. But it's fairly unlikely we'd
switch to something that was MORE contraining than in the past. Still,
don't play on the edge.

> The hardcodedness of arbitrary small limits that don't scale itself is
> also a problem:
> One cannot configure postgres to allow queries that the hardware is
> perfectly able of handling.
>
> It would be a bit like GCC giving up to compile a file if it contains more
> than 10000 words.
>

No, that's not an apt comparison at all. We cannot simply push the parser
to accept anything, regardless of the impact on other parts of the system.
Software engineering is all about tradeoffs. I agree with your point about
documentation, but this seems like trying to pick a fight.

Cheers,
Greg