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

Hi Shihao,

> At the end of recovery, a stale cache can only be in a process started
> before StartupLogicalDecodingStatus(), like the checkpointer. Later ones
> read it at startup, and a replayed enable waits for everyone.

I checked that directly for the failover-slot case (slot present before the
base backup, synced by slotsync, no status change replayed): with gdb I read
the process-local XLogLogicalInfo of every standby child just before
pg_promote(), over 8 runs. The only stale ones were the checkpointer, the
background writer, the two io workers and the startup process; every client
backend (including the one blocking promotion), the walreceiver and the
slotsync worker already had it set. So at end of recovery the barrier only
has to reach those auxiliaries, and the backend that blocks promotion is
already up to date. (I did not check whether any of those auxiliaries writes
WAL that needs the updated status.)

> It does not help when replaying the enable record has to wait, or
> when the end of recovery itself turns the status on. For master I
> agree (2) is better, and it helps DROP DATABASE too.

To check that (2) really clears these cases, I wrote a small,
measurement-only prototype of Sawada-san's idea on master (0497318e442):
in WaitForProcSignalBarrier(), after a fixed grace, a client backend or
walsender that still has not absorbed the barrier gets SIGTERM, as
pg_terminate_backend() does. It is not a patch proposal; the direction is
Sawada-san's and yours, and the diff is yours to use or ignore. (I built
your v5-0001 on the same base to compare the numbers below.)

Each case had one backend stuck in COPY TO STDOUT to a client that
stopped reading, a 2 s grace, and 3 runs:

- promotion, logical decoding enabled since startup: master hung, the
prototype promoted in ~2.02 s, v5 in 7-22 ms
- promotion after an enable record was replayed: the same
- replay of the enable record with the backend already stuck: master
and v5 stayed stopped for the whole 15 s, the prototype caught up in
~2.03 s
- pg_enable_data_checksums() on a primary: master was still
"inprogress-on" after 15 s, the prototype reached "on" in ~2.1 s
- DROP DATABASE with the backend connected to another database: master
hung for 20 s, the prototype dropped it in ~2.01 s

The stuck backend always ended with "terminating connection due to
administrator command". With a 5 s grace the times became ~5.0-5.1 s,
and the termination never came before the grace. As a negative control
I ran five idle sessions, one in pg_sleep() and one slow reader
(256 KB/s) through promotion, checksum enabling and DROP DATABASE: all
9 runs finished in 10-111 ms, and none of the 63 sessions was
terminated.

Two findings that may matter for the design:

- The recovery-conflict signal, as it is, does not reach such a backend.
The same prototype sending SignalRecoveryConflict() instead of SIGTERM
released none of them (0/12 across promotion, replay, checksums and
DROP DATABASE), since ProcessClientWriteInterrupt() acts only on
ProcDiePending. The same is true today for real conflicts: with
max_standby_streaming_delay = 1s, a snapshot conflict against that
backend kept replay stopped for the whole 15 s (3/3); the startup
process named it as the conflicting process, and confl_snapshot
stayed 0.

- For DROP DATABASE, a stuck backend connected to the target database
is not a barrier wait: a plain DROP fails after 5 s with "is being
accessed by other users", and WITH (FORCE) already works on master
(~115 ms). The barrier case is a backend on another database, as
Sawada-san said.

I can share the scripts, the diff and the per-run numbers if that is useful.

Regards,
Manu