cockroachdb / cockroachdb/cockroach

sql: lazily clean up stale entries in `system.table_statistics_locks`

Open
#164,794 1 comment 0 reactions 0 assignees View on GitHub
branch-master C-bug O-qa T-sql-queries
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

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.