cockroachdb / cockroachdb/cockroach

sql: multi-pod tenant temp object cleanup races on stale ListSessions, can drop active session's temp data

Open
#169,912 1 comment 0 reactions 0 assignees View on GitHub
A-multitenancy C-bug O-agent T-sql-foundations
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.