Re: [PG19] Promotion hangs behind a backend blocked on a stuck client

Hi Amit,

> If we anyway would like to implement killing of connections that
> didn't absorb barriers then do we even need v6. I am slightly hesitant
> about v6 because it adds more assumptions in the code like which
> processes can have stale cache.

With the docs note for 19 settled, I'd like to bring this back to
HEAD. v2 [1] implements option (2): once a barrier wait exceeds
procsignal_barrier_timeout (default 30s), a client backend or WAL
sender that hasn't absorbed it and is blocked in ClientWrite gets
SIGTERM. Nothing else is signaled.

To answer your question with numbers, I ran the same scenarios on
master (f2edf5dd00d) with three builds: unpatched, v6, and v2 (timeout
2 s for these runs), 3 runs per cell. The stuck backend is a session
whose client is alive but has stopped reading. The full matrix, with
how each row was measured, and the scripts to reproduce every row are
attached.

Promotion (failover slot synced from the start, local slot, slot
created or toggled during recovery, crash restart before promotion):
master hung every time, v6 promoted in 9-26 ms, v2 after its timeout.

Replay stuck behind the backend, then the primary creates its first
logical slot: master and v6 stalled replay for the whole 15 s, v2
caught up after its timeout.

On a primary (enabling checksums, DROP DATABASE of another database,
DROP DATABASE with a walsender in a base backup): master and v6 hung,
v2 finished after its timeout.

Controls (idle, pg_sleep, a slow reader, a sync rep waiter, a paused
COPY FROM STDIN) behaved the same in all three builds, and none was
terminated in 45 runs.

So v2 alone clears every case here, including the ones v6 doesn't
reach, and doesn't depend on v6. What v6 adds is speed: without it, a
promotion behind such a backend waits for the whole timeout. On the
stale caches, I read XLogLogicalInfo of every standby process with gdb
before and after pg_promote(). With the status on since startup,
master had the same five processes stale before promotion
(checkpointer, background writer, both io workers, startup) in every
run, and v6 had none, also after a crash restart. After promotion
nothing was stale in any build. That is evidence for these setups, not
a proof.

Two limits of v2. It doesn't change recovery conflicts: as Sawada-san
noted, a backend in ClientWrite doesn't service them, and with v1
replay still stopped behind one, 3/3; v2 only narrows whom it
terminates. And a session sending one large value to a slow client
for longer than the timeout is terminated too (documented in
config.sgml).

Reviews of v2 would be very welcome.

Regards,
Manu

[1] https://www.postgresql.org/message-id/179264658926.1656483.9003619221600313130%40gmail.com