With role = "auto", a write waiting on a dead primary stays there for the whole checkout_timeout after a new primary is elected #1741

PgDog version

main at 784f0b2.

Description

Split out of #1731 (issue 3). LoadBalancer::get_primary() checks out from the pool that is primary when the write arrives, and stays on it:

if let Some(pool) = self.primary() {
    return pool.get(request).await;
}

If that primary goes down, the write waits in its pool's queue for the whole checkout_timeout, even after the monitor has elected another server. Then it fails with checkout timeout. If the old server comes back as a replica first, the write gets a connection to it instead and fails with cannot execute ... in a read-only transaction. That is the sequence logged in #1494.

Reproduce

PostgreSQL 18.6, a primary and a streaming standby, both role = "auto", lsn_check_interval = 100, checkout_timeout = 10000, and one client inserting every 20 ms. Stop the primary with pg_ctl stop -m immediate and promote the standby 1.1 s later. The next write waits the full 10 s and fails with checkout timeout. Writes resume 8.8 s after the promotion. Two runs gave the same result.

Expected

A write waiting for the primary moves to the newly elected one within checkout_timeout.

Fix branch

https://github.com/roshangara/pgdog/tree/fix/lb-primary-follows-election (one commit, rebased on 784f0b2; also #1679)

• With automatic roles, get_primary() follows the election for up to checkout_timeout. It waits while there is no primary. When the elected pool changes, it drops its pending checkout and checks out from the new primary. Static roles are unchanged.
• The election channel is written only when the elected pool changes (send_if_modified). Otherwise every monitor run would restart the waiting checkouts.
• move_conns_to() publishes the carried-over primary, so a write after a reload doesn't wait for the next election.
• test_waiting_write_moves_to_new_primary and test_unchanged_election_does_not_wake_waiting_writes fail on main and pass on the branch.
• With the branch, writes resumed as soon as the standby was promoted. There was no checkout timeout in two runs.
• The lb, lsn_monitor and shard::monitor tests pass. cargo fmt --check and clippy -D warnings are clean.

Configuration

[general]
lsn_check_interval = 100
checkout_timeout = 10000

Two servers with role = "auto".