Statements on a pooled connection to a frozen primary hang without command_timeout #85

build_connection_factory(url, timeout) bounds the connect, but db-retry sets nothing that bounds a statement. When the primary freezes (process stopped, host unreachable, packets dropped) while the pool holds a connection to it, the next statement on that connection never returns. The caller waits until TCP keepalive closes the socket, about two hours with Linux defaults. postgres_retry never sees an exception, so it cannot help.

The failover drill from #84 shows this. With command_timeout in the DSN the pause scenario passes: the first statement times out after 3s, and the caller's next call reaches the promoted replica in 6 to 10s. Without it the scenario hangs.

Reproduction

1. In tests_dr/test_failover.py, change
   dsn = f"{DB_DSN}&command_timeout={COMMAND_TIMEOUT_SECONDS}" to dsn = DB_DSN.
2. Run:

   docker compose -f docker-compose.dr.yml run --rm dr uv run pytest tests_dr -s -k pause
   docker compose -f docker-compose.dr.yml down --remove-orphans --volumes

3. The INSERT in write(1), the first write after docker pause, blocks on the pooled connection until the 180s test budget cancels it.

What a bounded statement then does

With command_timeout set, asyncpg raises TimeoutError. postgres_retry does not retry it, which matches CONTEXT.md: the server may have run the statement, so it is not a retriable error. The caller has to decide whether to call again.

Options

• Document that a multi-host setup needs command_timeout (in the DSN query or connect_args), next to build_connection_factory in the README.
• Have build_connection_factory set a default command_timeout when the URL has none, for example equal to timeout or a separate argument.

The second option changes behaviour for anyone running long statements, so it needs a decision before any code. A fix starts with a failing test, and the drill already gives one.

Related: #67 covers connect-side timeouts in asyncpg's multi-host connect. This issue is about statements on a connection that is already open.