cockroachdb / cockroachdb/cockroach
sql: multi-pod tenant temp object cleanup races on stale ListSessions, can drop active session's temp data
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Problem**
On a multi-pod tenant, the temp object cleaner runs on every pod with no coordination. It uses `ListSessions` to find live sessions, but `ListSessions` fans out via each pod's *cached* `system.sql_instances`. If a pod's cache is briefly missing a peer, that peer's sessions are absent from the "active" set, and the cleaner deletes the live temp schemas of those sessions.
**Why it's broken**
`temporary_schema.go:692-707` gates system-tenant cleanup on the meta1 leaseholder but leaves tenant cleanup ungated, justifying it via `ListSessions`. That justification is wrong: the fanout target list comes from [`sqlInstanceReader`](https://github.com/cockroachdb/cockroach/blob/master/pkg/server/fanout_clients.go#L184)'s rangefeed-backed cache, which has no cross-pod consistency guarantee. [`waitForInstances`](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/temporary_schema.go#L714-L718) only blocks on the initial scan, not convergence.
**Introduced by**
[`21b5854b6077`](https://github.com/cockroachdb/cockroach/commit/21b5854b6077) (2021-08-27, "sql: add support cleaning temporary tables on different pods").
**Evidence**
#169663 — `TestTemporaryObjectCleaner` flake under external-process VC. Three concurrent cleaners called `ListSessions`; one got an inconsistent set and dropped an active session's `pg_temp_..._3` schema 45s before teardown confirmed the session alive.
**Exposure**
Low by default — both `sql.temp_object_cleaner.cleanup_interval` and `sql.temp_object_cleaner.wait_interval` are 30 min, so caches converge first. Real if a customer lowers `wait_interval`, after pod restarts before reader convergence, or under rangefeed pathology. Failure mode is silent data loss on a live session.
**Fix directions**
- Elect a single cleaner per tenant per cycle (instance-ID heuristic, sqlliveness lease, or table-row lease).
- Force reader convergence in the cleaner (direct `system.sql_instances` scan, or wait for a rangefeed checkpoint past a recent timestamp).
- Defense in depth: re-check `ListSessions` immediately before each delete.
Jira issue: CRDB-63710
Epic CRDB-17128
Contributor guide
Assessment
This issue has not been assessed yet.