PostgreSQL RETURNING, TCP Stalls, and Phantom Sessions

What I learned

UPDATE ... RETURNING * changes the transaction round-trip in a way that interacts badly with TCP stalls. A plain UPDATE commits and closes in one round-trip with no result to send back. With RETURNING *, PostgreSQL must assemble the result row and stream it to the client before the connection resolves — creating a window where a silently dead TCP connection can hold the session open for ~45 minutes.

How the stall propagates

1. PostgreSQL executes the UPDATE, writes the RETURNING result to its kernel TCP send buffer, and commits — releasing all row locks at this point.
2. If the TCP connection is dead, the data never reaches the client. PostgreSQL has no way to detect this until TCP keepalive fires.
3. Default Linux TCP keepalive: ~45 minutes of inactivity before the kernel probes and closes the dead connection.
4. During those 45 minutes the session stays visible in Performance Insights even though it holds no row locks and is doing no work.

sequenceDiagram
    participant G as gocpi
    participant PG as PostgreSQL

    G->>PG: UPDATE entity_party SET ... RETURNING *
    PG->>PG: Execute UPDATE, commit, release row locks
    PG-->>G: Write RETURNING result to TCP send buffer
    Note over G,PG: TCP connection silently dies
    Note over PG: Session stays open ~45 min<br/>until keepalive fires
    Note over G: Goroutine hangs waiting<br/>for response that never arrives

Lock:transactionid in Performance Insights

Every open PostgreSQL session holds an internal lock on its own transaction ID (Lock:transactionid) for bookkeeping. A session stuck waiting for TCP resolution keeps this lock alive — it surfaces in Performance Insights as Lock:transactionid with 0 rows/sec and near-zero calls/sec.

Warning

This is not a row lock. Lock:transactionid in Performance Insights does not mean another writer is blocked. The incident was confirmed not a row lock: a second writer updated the same row 11 minutes into the stall and completed in 100ms.

Tip

How to distinguish TCP stall from real lock contention — check whether other writers on the same row are also blocked. If they complete normally, the session is a zombie, not a blocker.

Why removing RETURNING fixes it

Without RETURNING, PostgreSQL has nothing to send back. The transaction commits and fully resolves in a single round-trip. A dead TCP connection at that point leaves nothing open on the PostgreSQL side.

See also

• gorm-returning-clause — the GORM-level fix: removing clause.Returning{} and adding a context timeout