Use byte-bounded spill files for materialized CTEs #26232

Is your feature request related to a problem or challenge?

Follow-up to #25580 (WITH ... AS MATERIALIZED), part of #17737. Related to #25910 and #25804.

When the body of a materialized CTE does not fit in memory, it is written to spill files, and each reference reads them back. Each reference reserves memory for the largest batch on disk. A spill file has no limit on the size of one batch in bytes, so one large batch (wide rows) sets the memory that every reference needs. A query with N references then needs N times the largest batch, and can fail with ResourcesExhausted when most of the pool is free.

#25580 has a partial fix that is local to the operator. When it moves an in-memory batch to disk, it writes the batch in pieces no larger than the batches already on disk (see the review thread #25580 (comment)). This has limits:

• The pieces are equal row ranges, sized from get_array_memory_size. This is wrong for dictionary, view and list columns, where the IPC writer writes more than the rows of a slice.
• Batches that go to disk while the body runs are written whole. The first large batch that is spilled sets the size for all later ones.

Describe the solution you'd like

#25910 adds a byte bound to SpillManager (with_max_batch_bytes), with a size model that agrees with the IPC writer. The bound is for readers that read a file to its end, which is what the CTE scans do.

When that PR is merged:

1. Create the SpillManager of the materialized CTE with a byte bound.
2. Remove the slicing in BufferedPartition::spill.
3. Keep the check that a move to disk releases more memory than it adds to the readers. It depends on the number of references and stays in the operator.

Open question: which bound to use. The sort derives it from sort_spill_reservation_bytes. The materialized CTE has no such setting. Options are a fraction of the memory pool divided by the number of concurrent readers, or a shared spill setting.

Describe alternatives you've considered

No response

Additional context

No response