Re: Set 1s WaitLatch timeout if standby limit has expired in ResolveRecoveryConflictWithBufferPin

Hello,

I just wanted to chime in and confirm that this race is not theoretical,
and I've observed it multiple times on version 18.4 in the last three
months, where it leads to rather unpleasant replication stalls.

I'm including the reproduction steps below. Given that any client could
unwittingly trigger this condition, I think it would be nice to
eventually merge this fix.

Setup
=====
Start with two-node replication setup where "standby" node replicates from
"primary"

(primary) CREATE TABLE bufferpin_test (id int PRIMARY KEY, pad text) WITH
(autovacuum_enabled = false);
(primary) INSERT INTO bufferpin_test SELECT g, repeat('x', 20) FROM
generate_series(1, 100) g; -- this should fit in a single page
(primary) DELETE FROM bufferpin_test WHERE id % 2 = 0; -- we've got
ourselves 50 dead tuples.

-- Wait until standby sees 50 live tuples:
(standby) SELECT count(*) FROM bufferpin_test;

Trigger the issue
=================
On standby, create a reader that will be stuck in `ClientWrite`. For
instance:

psql -h standby -d standby -c "COPY (SELECT t.id, g
FROM bufferpin_test t
CROSS JOIN LATERAL generate_series(1, 100000000 + t.id * 0) g)
TO STDOUT" | sleep 3600

(primary) VACUUM bufferpin_test; -- this should generate a Heap2 prune WAL
record.

At this point, we should see replication stall on standby. Wait for
max_standby_streaming_delay (default 30s). Postgres should be trying to
cancel our session that is stuck in the ClientWrite state.

(standby, new session): BEGIN; DECLARE c CURSOR FOR SELECT * FROM
bufferpin_test; FETCH 5 FROM c; -- This takes a second conflicting pin,
session is left idle in transaction. It was never signalled due to buffer
pin conflict yet.

Now kill the "sleep 3600".

We would still observe the replication stall due to the buffer pin held by
the "FETCH 5 FROM c".

It would not be resolved after another max_standby_streaming_delay
interval. The only way to resolve it is to either manually kill this query,
or (as a superuser, on standby), run "SELECT
pg_log_backend_memory_contexts((SELECT pid FROM pg_stat_activity WHERE
backend_type = 'startup'));". This will signal the startup process, which
will wake up and run solveRecoveryConflictWithBuferPin" and cancel "FETCH 5
FROM c"

Best regards, Dmytro

On Mon, Feb 23, 2026 at 11:00 AM Anthony Hsu <erwaman(at)gmail(dot)com> wrote:

> Thanks Álvaro for creating a commitfest entry. I've rebased my patch on
> master to fix the build issues. Also made some minor code comment changes
> and added a detailed commit message. Please let me know if you have any
> feedback or questions.
>
> -Anthony
>
> On Fri, Jan 30, 2026 at 9:42 AM Álvaro Herrera <alvherre(at)kurilemu(dot)de>
> wrote:
>
>> On 2025-Jul-06, Anthony Hsu wrote:
>>
>> > Hi,
>> >
>> > I think there is a race scenario where a backend holding a conflicting
>> > buffer pin isn't promptly canceled even when the standby limit has
>> expired:
>>
>> This patch seems to hav efallen through the cracks. I created a
>> commitfest entry for it,
>> https://commitfest.postgresql.org/patch/6445/
>>
>>
>>
>>
>> --
>> Álvaro Herrera PostgreSQL Developer —
>> https://www.EnterpriseDB.com/
>> "No tengo por qué estar de acuerdo con lo que pienso"
>> (Carlos Caszeli)
>>
>