SQL session refresh can close the connection pool irrecoverably ("sql: database is closed"); membership heartbeat then fails silently forever, leaving a zombie cluster that reports SERVING

[enasikbst]

Expected Behavior

Either the SQL session refresh recovers from every error state it can itself create, or a persistently failing membership heartbeat escalates (recreates its connection, or terminates the process so a supervisor can restart it). A cluster that cannot dispatch a single task should not report healthy.

Actual Behavior

On a self-hosted 4-role cluster (v1.31.2, Postgres via GCP Cloud SQL, one replica per role), the ringpop membership heartbeat began failing permanently:

UpsertClusterMembership operation failed. Error: sql: database is closed
  ringpop.(*monitor).startHeartbeatUpsertLoop → monitor.go:366

cluster_membership rows aged past the 20s liveness cutoff, matching/history/worker dropped out of the ring (tdbg membership list-gossip: frontend: 1, everything else 0), and the frontend answered every PollWorkflowTaskQueue / PollActivityTaskQueue with Unavailable: Not enough hosts to serve the request — for 29 hours, until a manual rolling restart. Throughout:

• all pods 1/1 Running, 0 restarts (the processes never crash);
• temporal operator cluster health reported SERVING the whole time — the gRPC health status is set once at startup (workflow_handler.go#L394-L401) and never re-evaluated against ring contents;
• no workflow failed, because none could start.

The database instance was healthy the entire time (no failover, no maintenance, concurrent backups completing; confirmed from the Cloud SQL operations log). The pool was closed from inside the Temporal process.

Root cause analysis (v1.31.2 source)

Two defects compound:

1. DatabaseHandle can close the live pool and never replace it

The session-refresh machinery introduced in #5926 (db_handle.go) is the only thing that closes the shared *sql.DB at runtime, and it has two problems:

• Destroy-before-throttle. reconnect(force=true) does h.db.Store(nil) and go prevConn.Close() before the 1-second throttle check; if throttled, it returns leaving no pool at all. And since ConvertError cannot tell which pool an error came from, a late-arriving error from an already-replaced pool destroys the freshly created healthy one. Under a burst of connection errors this churns: concurrent operations race h.db.Load() against go prevConn.Close() and observe Go's sql: database is closed.
• sql: database is closed is not a refresh trigger. ConvertError only reconnects on needsRefresh(err) (for Postgres: a *pgconn.PgError with a connection-failure SQLSTATE — driver/pgx.go#L80-L86), driver.ErrBadConn, io.EOF/ErrUnexpectedEOF, or ECONNRESET/ABORTED/REFUSED. Go's errDBClosed matches none of these. So the one error state the refresher itself creates is one it can never heal: the raw error is wrapped into serviceerror.Unavailable and retried forever against the same closed pool.

2. The membership heartbeat never escalates

startHeartbeatUpsertLoop (monitor.go#L358-L377) logs the error and sleeps 10–15s, unconditionally, forever — no consecutive-failure counter, no connection recreation, no fatal. Only startup failures are fatal (Start(), lines 142–151). Combined with (1), the process runs indefinitely with a permanently dead heartbeat. Recovery is further blocked because bootstrapRingPop only trusts rows with LastHeartbeatWithin: 20s (healthyHostLastHeartbeatCutoff), so once all pods' heartbeats fail, the discovery table is effectively empty.

Our incident logs are consistent with the handle spending most of the outage nil (no usable database connection found) with periodic races onto just-closed pools (54 × sql: database is closed over 29h in the history service alone).

Proposed fix

Any one of these breaks the failure chain; (1) + (3) together would both heal the pool and remove the zombie mode:

1. Treat sql: database is closed as refresh-triggering. In needsRefresh / ConvertError, classify the closed-pool error (it is not driver.ErrBadConn and not matchable via errors.Is — match Go's errDBClosed string, or probe with db.Ping on unclassified errors) so the handle heals the state it created itself.
2. Check the throttle before destroying the pool. In reconnect(force=true), evaluate the 1-second throttle first, and only then Store(nil) + close; additionally tag errors with a pool generation so a stale pool's error cannot destroy its healthy replacement.
3. Escalate a persistently failing membership heartbeat. In startHeartbeatUpsertLoop, count consecutive failures and logger.Fatal after N (e.g. 12 ≈ 2–3 minutes), matching the loop's own startup behavior — supervised deployments then self-heal instead of running as zombies.
4. Reflect ring membership in the standard gRPC health status. The ring-aware AdminService.DeepHealthCheck exists but nothing calls it by default; if the frontend's health status considered ring contents, operator cluster health and k8s probes could see this class of failure.

Prior art

• Stuck Temporal Server #6514 — same DatabaseHandle wedged on RDS Postgres in v1.25.0; sql: Only update last refresh time when we refresh #6538 fixed only the lastRefresh starvation, not the destroy-before-throttle ordering or the errDBClosed gap (both still present on main as of this writing — db_handle.go and monitor.go are byte-identical to v1.31.2).
• temporal-server "hang" during SIGTERM after loss of Cassandra connection #1867 — the exact Cassandra analog of the silent-heartbeat zombie (UpsertClusterMembership … session has been closed looping forever); open since 2021.
• CrashLoopBackOff on GKE cluster. #8202 — the same refresh machinery failing to recover after GKE node replacement (no usable database connection), the louder CrashLoop variant.

Steps to Reproduce the Problem

Hard to trigger deterministically because it requires losing the h.db.Load() vs go prevConn.Close() race, but the ingredients are:

1. Run any server role against Postgres (pgx driver) with default config.
2. Inject a burst of connection-level errors (e.g. kill backend connections repeatedly / brief network partition to the DB) so multiple concurrent operations trip ConvertError → reconnect(force=true) in close succession.
3. Observe operations begin failing with sql: database is closed or no usable database connection found; once the ringpop heartbeat is among them for >20s, membership empties and the frontend serves Not enough hosts to serve the request while operator cluster health still reports SERVING. The state persists until process restart.

Specifications

• Version: 1.31.2 (also inspected main; relevant files identical)
• Persistence: PostgreSQL (Cloud SQL, private IP, TLS), pgx driver, maxConns: 20, maxIdleConns: 20, maxConnLifetime: 1h
• Platform: GKE, one pod per role (frontend/history/matching/worker), official Helm chart 1.6.0