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

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)

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@kurilemu.de> wrote:

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@gmail.com> wrote:

Hi,

I reviewed v2. The bug is real on master and v2 fixes it. With
Dmytro's steps, master never cancels the cursor session. With v2 it
gets FATAL about one second after it takes the pin.

I had a few small comments, so I just made the changes. v3 is
attached.

- It calls WaitLatch directly in standby.c, so proc.c and proc.h are
not touched. That should be easier to backpatch.
- The comment no longer names PROCSIG_RECOVERY_CONFLICT_BUFFERPIN,
which is gone since 17f51ea8187.
- The 1s is a define now, and the commit message is shorter.

The patch in [1]/messages/by-id/44c24dcf-5710-410f-b1b6-d10b315f3d51@postgrespro.ru rewrites the same wait as a loop, so the two do not
apply together. Its expired path still sleeps with no timeout, so
this fix is still needed after it.

[1]: /messages/by-id/44c24dcf-5710-410f-b1b6-d10b315f3d51@postgrespro.ru
/messages/by-id/44c24dcf-5710-410f-b1b6-d10b315f3d51@postgrespro.ru

Regards,
Shihao

On 2 Oct 2026, Shihao Zhong wrote:

It calls WaitLatch directly in standby.c, so proc.c and proc.h are
not touched. That should be easier to backpatch.

We hit what looks like this bug today on a production standby running
our PostgreSQL 17.11 build. Both max_standby_*_delay settings were 30s,
but replay lag had already reached 5h41m. During investigation, startup
was waiting in BufferPin at an unchanged LSN. Following Dmytro's tip,
pg_log_backend_memory_contexts() woke it up. A buffer-pin recovery
conflict was logged, and the standby caught up without a restart.

I tested v3 on REL_17_STABLE, adapting the signal and wait-event enum
names. I used two cursors and SIGSTOP/SIGCONT on the first reader so
that the second pin was acquired after the cancellation broadcast.
Without the fix, the second reader survived cancellation of the first,
and replay needed the diagnostic wakeup. With v3, the second reader
was canceled in about a second, without an external wakeup.

I'll reopen the CF entry [0]https://commitfest.postgresql.org/patch/6445/. Please let me know if that's unnecessary
because this bug is already tracked elsewhere.

Thank you!

Best regards, Andrey Borodin.

[0]: https://commitfest.postgresql.org/patch/6445/