Hi, We had a backend OOM-killed in production, bringing down the database. It turned out to be an analytics query generated by an LLM, and nothing was ever executed - it was an EXPLAIN (FORMAT JSON). All of the work was in the planner.
It reached 2 TB RSS over about an hour. pg_terminate_backend() was issued more than 20 minutes before the OOM killer ended it, without effect. We analyzed the query and since it was generated by an LLM it was obviously complex and not written very well. Still, it was only ~10kB so it's not outside of the realm of possibilities that such a query is ever executed by anyone. We traced the issue to a reproducible scenario. A query with the following pattern is the problem: EXPLAIN (SUMMARY ON) WITH c0 AS (SELECT relpages::float8 AS x FROM pg_class), c1 AS (SELECT (x + x + x + x) AS x FROM c0), c2 AS (SELECT (x + x + x + x) AS x FROM c1), ... c11 ... SELECT x FROM c11; Planning time grows ~4.2x per level, matching the fanout: 6 levels 7.7 ms 7 levels 31.1 ms 8 levels 134.2 ms 9 levels 613.9 ms 10 levels 2570.7 ms At 12 levels with statement_timeout = '2s': t=4s backend active, 100% CPU, 3346 MB (timeout expired 2s ago) pg_cancel_backend() -> true, ignored t=8s 100% CPU, 6376 MB pg_terminate_backend() -> true, then ignored for a further 33.7s while RSS grew to 25.7 GB 41s wall in total, for a statement with a 2s timeout It seems like there's two issues here: * The query not checking for interrupts from pg_terminate_backend for a long time - in the real-world case for long enough for the database to get OOM killed. * A relatively simple query being allowed to consume this much memory in the first place. Should there be a bound on how much memory a query can consume during planning? It seems like I can get any arbitrary database to get an OOM kill (if OOM kill is configured) with a query like this. Below I've put some analysis from Claude after it ran the reproducer on a local db. I haven't verified those code paths, but it might help someone on the list here, so I have included it. -Floris >From Claude: There is exactly one CHECK_FOR_INTERRUPTS() on this path, at the top of pull_up_subqueries_recurse() (prepjointree.c:1278). It fires once per subquery level, and all the expensive work happens between two consecutive checks. In total src/backend/optimizer/ contains five: that one, two in predtest.c, and two in pathnode.c's add_path()/add_partial_path(). src/backend/nodes/ contains none at all -- no walker, mutator or copy function has one. Making that one check finer-grained would not be sufficient. Sampling the stack through one 12-level run shows pull-up finishing with roughly a third of planning still to go: t=6s .. 41s pull_up_subqueries_recurse t=43s .. 50s eval_const_expressions t=51s .. 53s convert_saop_to_hashed_saop t=54s .. 56s query_planner t=58s .. 59s grouping_planner t=61s .. 62s set_plan_references / fix_scan_expr (then executor startup) Everything after pull-up lives in clauses.c, planner.c, planmain.c and setrefs.c, which have no CHECK_FOR_INTERRUPTS between them, so ~20s of that run is unresponsive no matter how often the pull-up recursion checks. (gdb attach inflates the total; the proportions are the point.) I am not sure what the right fix is. CHECK_FOR_INTERRUPTS() in expression_tree_walker_impl()/expression_tree_mutator_impl() and copyObjectImpl() would cover it, but those are relatively hot; an amortised check every N nodes would be cheaper. Separately, nothing bounds planner memory in aggregate, so there is no ceiling short of the OOM killer -- but that seems like its own thread.
