Repository navigation
isopen reports a terminated connection as healthy until the next query fails #13
Description
Related to the #6 bullet "Add protocol-safe dead-peer validation for pooled connections without retrying ambiguous user operations." This is a reproducer for it, plus the retry-safety classification I think that bullet is pointing at. Happy to send a PR if the shape below looks right to you.
What happens
If the server terminates a backend while its connection sits idle in a pool, isopen keeps returning true. The state only flips after a user query has already failed — so a pool that validates with isopen hands the dead connection to the next caller, and that caller absorbs the failure.
using Postgres, DBInterface, Tables
const DSN = "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=postgres sslmode=disable"
victim = DBInterface.connect(Postgres.Connection, DSN)
killer = DBInterface.connect(Postgres.Connection, DSN)
pid = only(Tables.rowtable(DBInterface.execute(victim, "SELECT pg_backend_pid() AS p"))).p
DBInterface.execute(killer, raw"SELECT pg_terminate_backend($1)", (pid,))
sleep(0.5)
isopen(victim) # true <- connection is already dead
try
DBInterface.execute(victim, "SELECT 1")
catch e
e.code # "57P01" terminating connection due to administrator command
end
isopen(victim) # false <- only nowObserved on Postgres v2.1.0, Julia 1.12.7, PostgreSQL 16.15.
The same shape arises from a server restart, a failover, an idle-session timeout (57P05), or an operator pg_terminate_backend sweep — i.e. the ordinary lifetime of a long-lived pool, not just a test.
This affects Postgres.ConnectionPool directly, so it is not only a downstream concern.
Why the state is knowable before the query
In the case above the server sends a FATAL ErrorResponse and closes; those bytes are already sitting in the socket buffer while the connection is idle. A validation routine that performs a non-blocking read of pending input on an idle connection can therefore observe the termination before handing the connection out, without a round trip and without sending anything.
This mirrors how libpq solves it: PQstatus alone is not enough because it reports a cached state that only changes after an I/O attempt fails, so a client has to call PQconsumeInput first — a non-blocking read that flips the status to CONNECTION_BAD on EOF. The remedy carries over to a native implementation even though the mechanism does not.
What this does not catch is a silent drop that delivered no bytes at all (a severed network path). That one can only be handled reactively, after some statement has actually failed.
The "without retrying ambiguous operations" half
This is the part I think deserves care, because getting it wrong is a data-corruption bug rather than an availability bug. A dead-peer signal is only safe to convert into a transparent retry when the statement provably never ran. Not every connection-shaped SQLSTATE clears that bar.
The classification below is what we settled on downstream after getting it wrong once:
Safe — the backend is gone and the statement never executed:
| SQLSTATE | |
|---|---|
08000 |
connection_exception |
08001 |
sqlclient_unable_to_establish_sqlconnection |
08003 |
connection_does_not_exist |
08004 |
sqlserver_rejected_establishment_of_sqlconnection |
08006 |
connection_failure |
57P01 |
admin_shutdown — pg_terminate_backend, fast shutdown |
57P02 |
crash_shutdown |
57P03 |
cannot_connect_now — server still starting |
57P05 |
idle_session_timeout — the one that specifically targets idle pooled connections |
Deliberately excluded, despite looking like near neighbours:
| SQLSTATE | why not |
|---|---|
08007 |
transaction_resolution_unknown — the commit outcome is unknown; re-running double-applies |
08P01 |
protocol_violation — the wire is broken, but the statement may well have run |
57014 |
query_canceled — the session is alive; retrying defeats statement_timeout and explicit pg_cancel_backend |
57P04 |
database_dropped — retrying is futile |
40001 / 40P01 |
serialization_failure / deadlock_detected — the caller must retry the whole transaction; re-running one statement on a fresh autocommit session is the corruption case |
25P03 |
idle_in_transaction_session_timeout — only occurs inside a transaction, where a statement-level retry is wrong anyway |
Excluding those costs no classification fidelity — they should still surface to the caller as errors — only the automatic retry.
Two further constraints that fall out of the same reasoning:
- Validate only a connection with no statement in flight. A probe that consumes input while a result is pending would eat that result. In a pool this is naturally satisfiable: probe on checkout of an idle slot, never on a leased one.
- Consuming is not discarding. The probe should only move already-arrived bytes into the read buffer, so a legitimately pending result is preserved rather than dropped.
Proposed shape
Something like Postgres.isvalid(conn) (or a keyword on the existing pool checkout) that:
- performs a non-blocking read of pending input on an idle connection,
- returns
falseon EOF or on aFATALErrorResponse observed that way, - leaves any other pending bytes buffered and untouched,
- is a no-op for a connection with a statement in flight.
and keeps the retry decision separate from the liveness decision, so a caller can distinguish "this connection is dead" from "it is safe to transparently re-send what I was about to send".
Context
This came out of evaluating Postgres.jl as a backend for PormG, an async-first Julia ORM that currently drives PostgreSQL through LibPQ.jl. Its connection pool hit exactly this blind spot on the libpq side, which is why the classification above already exists in a tested form. If a PR along these lines would be welcome, I'm glad to write it — say which shape you prefer and I'll follow it.
Activity
quinnj commentedon Sep 23, 2026
[this fix explanation generated by AI]
Thanks for the detailed report and the reproducer. This is fixed in Postgres.jl v2.2.0 (#14). It needs Reseau.jl v1.5.0 (JuliaServices/Reseau.jl#163). Both versions are registered.
What changed
New
Postgres.isvalid(conn), close to the shape you proposed. It checks an idle connection without sending anything and without waiting on the network:false(and closes the connection) on EOF, on anyErrorResponse(such as the57P01/57P05FATAL), on a message that cannot occur on an idle session, or on a transport or protocol error.NotificationResponse,NoticeResponse, andParameterStatusmessages that arrive while idle are handled as usual: notification and notice callbacks run, and server parameters update. The connection stays valid.trueand touches nothing.It is declared
publicbut not exported, and it does not extendBase.isvalid. Call it asPostgres.isvalid.ConnectionPooluses it onacquire(before it hands out an idle connection) and onrelease. A connection that fails the check is replaced, not reused. Validation runs after the pool lock is released, while the connection is exclusively leased, so callbacks it triggers cannot block other pool users.isopen(conn)is unchanged. It still reports only local socket state. Its docstring now points toPostgres.isvalid.How the non-blocking read works
This is the native equivalent of libpq's
PQconsumeInputstep.TCP.tryread!andTLS.tryread!. They return a byte count,0on EOF, ornothingwhen no data is ready. They never block.API.BufferedConn. Validation and normal protocol reads share its buffer. That is how bytes read during validation stay available to the next query.A deadline-based probe was not an option: Reseau checks the read deadline before each
recv, so a past deadline never sees queued bytes, and a future deadline would make every checkout wait.A wire detail that may matter for PormG too
On an immediate shutdown (
pg_ctl stop -m immediate) or a crash, PostgreSQL sends aNoticeResponse(a WARNING) and then closes. It sends no FATALErrorResponse. So EOF detection matters as much as theEcheck.isvalidhandles both cases.Related hardening in the same PR
Message bodies are now read with an exact-length read. Before, a short read at EOF could let a truncated message parse. Now a truncated message closes the connection with an error.
Retry classification
This part is deliberately not in the fix.
isvalidanswers only "is this connection known to be dead?" It never retries a user operation, and the pool does not retry either. So liveness and retry safety stay separate, as you suggested. Your SQLSTATE table is a good basis for a caller-side retry policy;Postgres.Errorcarries the SQLSTATE. If you want a helper for it inside Postgres.jl, a separate issue is the best place to discuss it.Limits
truemeans no failure was observed. It does not prove the server is alive.Tests
pg_terminate_backendon an idle connection,isopenis stilltrue,isvalidreturnsfalse, and thenisopenisfalse. ALISTENconnection that receives a notification while idle stays valid, and the notification callback runs. A pooled connection terminated while idle is replaced at the nextacquire.ReadyForQuery, unsolicitedERROR, truncated message); partial messages kept across validation at several split points; validation callbacks that do not hold the pool lock; a throwing validation that returns its pool permit.Validate idle sessions with buffered non-blocking reads (JuliaDatabas…