External sort can lose reserved spill workspace when memory availability decreases

[sunchao]

Describe the bug

An external sort can fail with ResourcesExhausted while trying to spill, even though it already reserved enough workspace for that spill. This can happen when the memory pool's allocation limit decreases or other consumers take the capacity that the sorter releases.

ExternalSorter acquires sort_spill_reservation_bytes before buffering input, but spill preparation frees that reservation. The in-memory merge then starts with an empty reservation, and its cursors and encoded rows request memory from the pool again. Previously granted workspace is therefore unavailable precisely when the sorter needs it to free buffered input.

For an illustrative example, suppose a sorter holds 80 MiB for input and 16 MiB for spill workspace. The pool subsequently lowers its allocation ceiling to 64 MiB while allowing existing reservations to remain. Freeing the workspace leaves 80 MiB reserved; a new 1 MiB cursor allocation can fail even though it would have fit within the 16 MiB already acquired for spilling.

To Reproduce

A deterministic regression can exercise the failure without relying on concurrent scheduling:

1. Use a test MemoryPool with an adjustable allocation ceiling that leaves existing reservations intact when the ceiling decreases.
2. Buffer multiple batches with two sort keys, including a string column, and disable the concatenate-and-sort shortcut so spilling constructs row cursors.
3. After the sorter acquires its input and spill reservations, lower the ceiling below the current reserved total.
4. Submit another batch to trigger spilling, then drain the sorted output.

The spill should use its existing workspace and reduce memory consumption. The current path can instead fail when a cursor or encoded-row reservation requests fresh capacity.

I verified this against Apache DataFusion ee59f628b44eb80e8a4f126288632ef51fda5dc2 (55.0.0, Arrow 59.2.0) with a local test-only fixture and otherwise unchanged production code. The fixture uses 30 slices of one 30,720-row parent batch, with 1,024 rows per slice, nullable UTF-8 and decimal sort keys, and a string payload. It sets the output batch size to 128, the spill workspace to 64 KiB, and the in-place sort threshold to zero. The initial pool capacity is 64 KiB plus the reservation estimates for the first nine slices and half the estimate for the tenth slice. After nine batches have been buffered, it lowers the allocation ceiling to 75% of its initial value, below the bytes already reserved.

The next insertion triggers spilling and fails with:

ResourcesExhausted("allocation limit reached")

The same regression passes with a local candidate fix that keeps the already acquired workspace available to the spill merge. It verifies the complete sorted rows and payloads against a reference sort, successful spilling, and reservation and spill-file cleanup. The fixture and candidate fix are available in #24740. The fixture deliberately overrides the settings above to isolate this path; this is not a claim that a default-settings SQL query always reproduces the failure.

Expected behavior

Keep already acquired spill workspace available across the sorting and merging needed to write a spill. Allocations that exceed that workspace must still respect the parent pool's limits. Release reservations when they are no longer needed, including on errors and cancellation.

Additional context

This is a follow-up to #20642, which preserved the reservation for the final disk merge. The release during preparation of an in-memory spill remains in the upstream revision linked above.

Related reports include #19013, #16979, and #19216. This issue isolates the reservation lifetime problem; it does not establish that all failures in those reports have the same cause.