stdlib: failed prepare is deallocated by SQL text instead of its server-side statement name — leaked statement, 42P05 re-poisoning, and errors surfaced on unrelated queries

[eliranbz]

Version

pgx v5.10.0 (the relevant code is unchanged on master as of 2026-08-28).
Driven through database/sql (jackc/pgx/v5/stdlib), as used by GORM with
PrepareStmt: true. PostgreSQL 16.

The mechanism

stdlib.Conn.PrepareContext calls c.conn.Prepare(ctx, query, query) — i.e.
name == sql. In pgx.Conn.Prepare that path derives a digest name for the
wire protocol:

if name == sql {
    digest := sha256.Sum256([]byte(sql))
    psName = "stmt_" + hex.EncodeToString(digest[0:24])
    psKey = sql
}
...
sd, err = c.pgConn.Prepare(ctx, psName, sql, nil)
if err != nil {
    var pErr *pgconn.PrepareError
    if errors.As(err, &pErr) {
        c.failedDescribeStatement = psKey   // ← the SQL text, not psName
    }
    return nil, err
}

On the next Prepare on that connection, the cleanup introduced in 5.8.0
("Fix: Handle for preparing statements that fail during the Describe phase")
runs:

if c.failedDescribeStatement != "" {
    err = c.Deallocate(ctx, c.failedDescribeStatement)
    ...
}

Conn.Deallocate(ctx, name) maps name → sd.Name via
c.preparedStatements[name] — but a FAILED prepare was never stored there, so
it falls through to psName = name and sends the protocol Close message
with the full SQL text as the statement name. No statement by that name
exists, closing a nonexistent statement is not an error, so the cleanup
"succeeds" while closing nothing.

Consequences

When the original PrepareError had ParseComplete == true (Parse succeeded,
the error arrived during Describe — e.g. a statement_timeout or
pg_cancel_backend landing between Parse and Sync):

1. The server-side statement stmt_<digest> leaks — the deallocation that
   was supposed to remove it targets the wrong name.

2. Any retry of the same SQL text on that connection is permanently broken:
   c.preparedStatements[sql] is empty, so Prepare sends Parse for
   stmt_<digest> again → 42P05 duplicate_prepared_statement → that is
   itself a PrepareError, so failedDescribeStatement is set again → the
   next cleanup no-ops again → loop, for the remaining lifetime of the
   connection. This is precisely the failure class the 5.8.0 fix was meant to
   close (Occasional prepared statement already exist errors when using statement timeout #2223), still reachable through the stdlib path.

3. The cleanup's own failure surfaces on innocent queries. The deallocate
   round-trip (Close + Sync + read) runs at the START of whatever query
   next needs a prepare on that pooled connection. If it fails (context
   deadline, network hiccup), that unrelated query fails with

   failed to deallocate previously failed statement "SELECT ... <the OLD query's full SQL> ...": <cause>
   

   We hit this in production: an attendance-ingest query's prepare failure
   poisoned a pooled connection, and a completely unrelated background job
   that later borrowed the connection died with an error quoting the ingest
   query's SQL — genuinely confusing to debug from the victim's side.

Where the fix probably belongs

failedDescribeStatement stores psKey, but the deallocation needs psName
(what was actually sent in the Parse message). Remembering the psName
alongside (or instead of) the psKey — and having the cleanup deallocate by the
server-side name — closes both the leak and the 42P05 loop:

// on failure:
c.failedDescribeStatement = psName   // or a second field carrying psName

(The QueryExecModeCacheStatement path is unaffected: there name is
stmtcache.StatementName(sql) and psName == psKey, so the deallocation
already targets the right name. Only the name == sql route — the
stdlib/database-sql route — has the mismatch.)

Separately, it may be worth discussing whether a failed cleanup should fail
the innocent caller at all, or instead invalidate the connection (as other
unrecoverable states do) so the pool discards it.

Repro sketch

Timing-dependent (needs the server error to land after Parse completes), so
this is probabilistic rather than a unit test:

db, _ := sql.Open("pgx", dsn) // or GORM with PrepareStmt: true
db.Exec("SET statement_timeout = '5ms'")
// Prepare statements with large/slow-to-plan text under load until one
// PrepareContext returns a PrepareError with ParseComplete == true.
// From then on, on that pooled connection:
//   - re-preparing the SAME SQL fails with 42P05 (consequence 2)
//   - SELECT name FROM pg_prepared_statements shows the leaked stmt_<digest>
//     (consequence 1)

The naming mismatch itself (consequence 1) is directly visible without timing
tricks by inspecting what Conn.Deallocate sends when handed a key that is
not in c.preparedStatements: the Close message carries the raw key.

Happy to provide more detail from our production traces if useful.