Re: "memory exhausted" in query parser/simplifier for many nested parentheses
| От | Greg Sabino Mullane |
|---|---|
| Тема | Re: "memory exhausted" in query parser/simplifier for many nested parentheses |
| Дата | |
| Msg-id | CAKAnmmJZbU0htYb9fN8amRiUEJ9BAoTkbvrdw8f3ZT8zgMjSjw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: "memory exhausted" in query parser/simplifier for many nested parentheses (Niklas Hambüchen <mail@nh2.me>) |
| Ответы |
Re: "memory exhausted" in query parser/simplifier for many nested parentheses
Re: "memory exhausted" in query parser/simplifier for many nested parentheses |
| Список | pgsql-bugs |
On Fri, Dec 13, 2024 at 8:53 AM Niklas Hambüchen <mail@nh2.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
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
В списке pgsql-bugs по дате отправления: