Ring prunes starve under claim load: claim_ready_runtime locks every slot child, so the 10 ms ACCESS EXCLUSIVE never wins #721

On 0.6.16 under sustained claim load, the queue-ring and claim-ring prunes can never take their ACCESS EXCLUSIVE locks, so the rings stop rotating and do not recover by themselves.

What happens

Before truncating a sealed slot, prune_oldest runs LOCK TABLE <slot's 8 children> IN ACCESS EXCLUSIVE MODE under prune_lock_timeout, which defaults to 10 ms. claim_ready_runtime() runs as a cached generic plan inside a plpgsql function. That plan locks every child of the partitioned parents it reads, sealed slots included. Each claim call therefore holds AccessShareLock on the slot the prune wants for the whole statement.

Once claims stay concurrent, there is never a 10 ms window with no claim holding the slot. Every prune attempt ends skipped_busy, the ring stays on its current slot, and that slot keeps growing. A larger slot makes claims slower, which keeps more claims overlapping. The loop sustains itself after whatever first blocked the prune has cleared.

Evidence (PostgreSQL 18.4, 14 runtimes on 0.6.16, about 13 queues)

• Queue ring: stuck on one slot for about 2 days. That ready child holds about 55M rows (64 GB) and its segments child about 28M rows, roughly one row per segment. The first blocker was queue.unclosed_claim_refs: orphaned receipt claims from an earlier version. 0.6.16's rescue closed them, and the count proof for the next slot now balances: 8,729,498 claims = 8,729,412 explicit closures + 86 compact closures. After that, the outcome became skipped_busy instead.

• awa_maintenance_prune_attempts_total{awa_ring="queue"}, per 10 minutes:

  Outcome	Blocker or reason	Count
  skipped_busy	every one of queue.ready_rows, queue.ready_segments, queue.done_rows, queue.claim_attempt_batches, queue.receipt_completion_batch_rows, queue.terminal_delta_rows	about 160–285
  backed_off	—	about the same
  skipped_active	unclosed_claim_refs	0

  The claim ring shows the same pattern on claim.rows, claim.closure_rows and claim.closure_batch_rows.

• Lock sampling on the primary: pg_locks on the next slot's ready, done and segments children, sampled 200 times at 25 ms intervals, never found the slot free. It always had 4–6 holders, all AccessShareLock, all different short transactions running SELECT … FROM awa.claim_ready_runtime($1, $2, $3, $4), and spread across several runtimes and queues, including a queue with no available backlog.

• pg_stat_statements for that claim statement: about 16.8M calls over 4.4 days, 31.6 ms mean, 60 ms stddev, 5.4 s max. During the stall, individual calls took about 20–110 ms.

• The prune's own pre-lock count proof (queue_prune_has_unclosed_claim_refs_tx) takes about 6 s per run on the leader; the claim-ring proof about 1.4 s.

A second hazard if the lock is acquired

After LOCK TABLE succeeds, prune_oldest re-runs queue_prune_has_active_leases_tx, queue_prune_has_pending_ready_tx and queue_prune_has_unclosed_claim_refs_tx while holding ACCESS EXCLUSIVE. With the count proof at about 6 s, a successful lock would stall every claim, completion and admin read touching those parents for at least 6 s. That makes raising prune_lock_timeout unsafe as a workaround.

Possible directions

1. Lock only what the hot paths read. Claim, complete and heartbeat should lock only the slot children they read, for example with per-child dynamic SQL instead of a generic plan over the parent, so a sealed slot never conflicts with live traffic. This removes the starvation at its root.
2. Keep expensive proofs out of the exclusive section. Prove "closed" before locking. Under the lock, only re-check cheap invariants: no new rows since a high-water mark, and that the generation is unchanged.
3. Give the prune a way to win under sustained load. For example, escalate the lock timeout after N consecutive skipped_busy outcomes, bounded and only once (2) holds. Or use a short advisory-lock barrier that claimers take shared, which the prune takes exclusively for one attempt.
4. Count proof (not the blocker here, but adjacent): its cost grows with every claim that ever referenced the slot, and it requires exact equality with no fallback. Any permanent drift between claims and closures pins the slot forever. A per-slot open-claim counter, or an exact anti-join over a bounded range, would bound both problems.

A persona that sustains overlapping claims from several runtimes while a slot is sealed should show it: assert the ring rotates within a bounded number of prune attempts, and that claim p99 doesn't spike when the prune succeeds.

Related: #720 bounds the admin views that time out on the oversized slot.

Status on main

#515 (v051, main only) moves the claim CTE into per-slot claim_ready_slot_N functions that read the slot's children directly. That removes the sealed slot from the claim CTE's locks, and faster claims mean fewer overlapping holders, but it does not remove the conflict:

• claim_ready_runtime still runs its lane probe on every call before dispatching, and that probe reads the ready_segments parent with no ready_slot predicate (first_segment: WHERE queue = … AND priority = … AND enqueue_shard = … AND next_lane_seq > claim_seq ORDER BY next_lane_seq LIMIT 1). Neither a custom nor a generic plan can prune it, so every claim holds AccessShareLock on every segments child, sealed slots included.
• prune_oldest on main takes LOCK TABLE {segment_child} IN ACCESS EXCLUSIVE MODE first, under the same 10 ms prune_lock_timeout, then the other children NOWAIT. Concurrent claims therefore still starve it.
• The cursor catch-up, head and spent-prefix checks in the dispatcher also read the ready_entries, ready_tombstones, ready_claim_attempt_batches, lease_claims, done_entries (and other) parents. They run only on those branches, but when they do they lock every child too.
• The post-lock re-run of queue_prune_has_unclosed_claim_refs_tx under ACCESS EXCLUSIVE is unchanged.

So the fix still needs the lane probe (and the conditional checks) to address only live slots — for example probing segments slot by slot from the ring's live range, or keeping the per-lane "next segment" in an unpartitioned table — plus moving the expensive proof out of the exclusive section. #515 is not in the 0.6 line.