cockroachdb / cockroachdb/cockroach
sql: lazily clean up stale entries in `system.table_statistics_locks`
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We introduced `system.table_statistics_locks` to coordinate concurrent auto stats jobs by enforcing per-table and global concurrency limits. This issue raises two minor suggestions:
1. Clean up orphan entries for dropped tables, to avoid accumulating unused rows.
2. Clean up entries with job IDs for jobs that are no longer running.
Point 1 is straightforward. Point 2 covers an edge case where `createStatsResumer` fails in `releaseAutoStatsConcurrencyLocks()`, e.g. due to a node crash. Stale job IDs in the table-level row would block new auto stats on that table, and stale IDs in the global row (table_id=0) would consume concurrency slots permanently.
Given that it's a narrow scenario, I don't think it's worth adding job-liveness filtering to `checkStatsJobsQuery`. A lazy periodic cleanup of stale entries would be simpler.
(All being said, both cases are fairly edge-casy, so feel free to push back.)
----
QA test that inspired point 2:
```
SET allow_unsafe_internals = true;
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;
SET CLUSTER SETTING sql.stats.error_on_concurrent_create_stats.enabled = true;
CREATE TABLE t (id INT PRIMARY KEY, val INT);
INSERT INTO t SELECT generate_series(1, 100), generate_series(1, 100);
-- upsert a fake job.
UPSERT INTO system.table_statistics_locks (table_id, kind, job_ids)
VALUES (
(SELECT id FROM system.namespace WHERE name = 't' AND "parentSchemaID" != 0),
1,
ARRAY[999999999]
);
-- This fails, even though job 999999999 does not exist.
CREATE STATISTICS __auto__ FROM t;
-- ERROR: another CREATE STATISTICS job is already running
```
Jira issue: CRDB-61020
Contributor guide
Assessment
This issue has not been assessed yet.