Summary

This PR fixes three related bugs in the buffer pool's eviction queue purge path. Together they caused dead nodes to silently accumulate in the eviction queue, alive-but-pinned nodes to be dropped (disturbing LRU and corrupting the dead-node counter), and the multi-iteration purge loop to make essentially no progress under contention.

The fix is small (3 files, +37 / −16) but the underlying issues interact and are worth fixing together so the purge accounting becomes self-consistent again.

Background

EvictionQueue uses a duckdb_moodycamel::ConcurrentQueue<BufferEvictionNode> where each node holds:

• weak_ptr<BlockMemory> — the block this entry refers to.
• handle_sequence_number — incremented every time the block is re-added to the queue.

A node is logically dead if either:

1. The weak_ptr is expired (the BlockMemory was destroyed), or
2. The block's current eviction_seq_num is newer than the node's recorded sequence number (a newer entry superseded it).

Dead nodes are never proactively removed — they are dequeued lazily by EvictionQueue::Purge(), which runs on one thread at a time and tries to keep the queue's dead-node ratio under control.

Bugs fixed

1. IncrementDeadNodes was skipped when the buffer was already null

BlockMemory::~BlockMemory only called IncrementDeadNodes when GetBuffer() returned non-null:

```cpp
// before
if (GetBuffer() && GetBufferType() != FileBufferType::TINY_BUFFER) {
GetBufferManager().GetBufferPool().IncrementDeadNodes(*this);
}
```

But the queue entry's lifetime is independent of whether the buffer is currently loaded — only the dequeue side ever removes it. If the block had previously been evicted (buffer = nullptr) and then the BlockMemory itself was destroyed, the queue entry that pointed at it became dead but was never accounted for. This systematically under-counted total_dead_nodes, which made the purge ratio early-out (approx_alive_nodes * (ALIVE_NODE_MULTIPLIER - 1) > approx_dead_nodes) fire too often and let dead entries accumulate.

Fix: drop the GetBuffer() guard.

2. Pinned-but-current nodes were classified as dead during purge

PurgeIteration used TryGetBlockMemory() to decide whether a dequeued node was alive:

```cpp
shared_ptr BufferEvictionNode::TryGetBlockMemory() {
auto shared_memory_p = memory_p.lock();
if (!shared_memory_p) {
return nullptr;
}
if (!CanUnload(*shared_memory_p)) { // also checks readers / pinning
return nullptr;
}
return shared_memory_p;
}
```

CanUnload is not a deadness predicate — it also returns false when the block is currently pinned (readers != 0). So a perfectly valid latest-version node that just happened to be pinned at the moment of the purge was:

• Dropped from the queue (LRU position lost).
• Counted toward total_dead_nodes -= actually_dequeued - alive_nodes, decrementing the dead counter for a node that was never dead.

This actively desyncs total_dead_nodes from reality and combines badly with bug.

Fix: introduce a dedicated BufferEvictionNode::IsDeadNode() that checks only the two real deadness conditions (expired weak_ptr or stale sequence number) and use it for purge classification:

```cpp
bool BufferEvictionNode::IsDeadNode() {
auto shared_memory_p = memory_p.lock();
if (!shared_memory_p) {
return true;
}
if (handle_sequence_number != shared_memory_p->GetEvictionSequenceNumber()) {
return true;
}
return false;
}
```

PurgeIteration now decrements total_dead_nodes by an explicit dead_count, which can never include pinned-but-current entries.

3. Multi-iteration purge churned the purge thread's own moodycamel sub-queue

This is the most impactful bug.

moodycamel::ConcurrentQueue is not a single FIFO. Internally it maintains one sub-queue per producer thread (implicit producers are created lazily on first enqueue from a given thread). try_dequeue_bulk walks the producer list and tends to drain the most recently used sub-queues first.

The previous purge loop re-enqueued alive nodes between iterations:

```cpp
// PurgeIteration (old)
q.enqueue_bulk(purge_nodes.begin(), alive_nodes);
```

That enqueue happens on the purge thread, which in nearly all interesting scenarios was not previously a producer for this queue (producers are worker threads). It creates an implicit-producer sub-queue owned by the purge thread, and on the next try_dequeue_bulk call moodycamel preferentially re-drains it — pulling back the very alive nodes the purge just inserted, instead of reaching dead entries sitting in worker producer sub-queues. Under sustained pressure the loop made little to no real progress: it kept "purging" the same alive nodes round and round while real dead entries accumulated elsewhere.

Fix: accumulate alive nodes into a local vector across the entire Purge() loop and re-enqueue them once at the end. The early-out queue-size estimate is compensated with the size of the held-aside vector so the early-out check still reflects the true logical queue size:

```cpp
vector alive_nodes_to_reenqueue;
while (max_purges != 0) {
PurgeIteration(purge_size, alive_nodes_to_reenqueue);
approx_q_size = q.size_approx() + alive_nodes_to_reenqueue.size();
// ... ratio / early-out checks unchanged ...
max_purges--;
}
if (!alive_nodes_to_reenqueue.empty()) {
q.enqueue_bulk(alive_nodes_to_reenqueue.begin(), alive_nodes_to_reenqueue.size());
}
```

This guarantees that each purge iteration sees fresh entries from worker sub-queues instead of bouncing the purge thread's own buffer.

How they interact

Bugs 1 and 2 push the counter in opposite directions, masking each other intermittently and making the symptom hard to trace. Bug 3 means even when the ratio check correctly demands more aggressive purging, the loop fails to actually purge.

Files changed

• src/include/duckdb/storage/buffer/buffer_pool.hpp — declare BufferEvictionNode::IsDeadNode.
• src/storage/buffer/buffer_pool.cpp — implement IsDeadNode, restructure Purge / PurgeIteration to defer re-enqueue.
• src/storage/buffer/block_handle.cpp — drop GetBuffer() guard before IncrementDeadNodes.

Test plan

• make reldebug builds cleanly.
• build/reldebug/test/unittest passes (full unit suite).
• Targeted buffer-manager / eviction tests pass (build/reldebug/test/unittest "[buffermanager]").
• Local repro of dead-node accumulation under heavy churn no longer grows total_dead_nodes unboundedly between purges.
• LRU-sensitive workloads do not show pinned blocks being repeatedly evicted/reloaded after purge.
• Multi-iteration purge actually shrinks the queue under contention (verified by observing q.size_approx() between iterations in a stress harness).

Notes for reviewers

• BufferEvictionNode::IsDeadNode() is intentionally not const because weak_ptr::lock() is a mutating operation only in spirit — happy to mark it const if preferred (it would still compile since lock() is const on the weak_ptr).
• The held-aside vector is local to a single Purge() invocation and bounded by the total size of dequeued nodes, so memory overhead is at most one purge_size worth of entries beyond what purge_nodes already held.
• Re-enqueuing once at the end means alive nodes briefly leave the queue during purge. Any concurrent TryDequeueWithLock on the same queue is gated by purge_lock (held for the entire Purge call), so no observer can see the queue mid-purge.