OOM kill during planning - pg_terminate_backend is ignored

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.