sql: Automatic sql consistency checker

[vivekmenezes]

We already check replication consistency on an ongoing basis and can check replication consistency explicitly through a KV API command CheckConsistency(). This issue is about developing a system that checks the consistency of UNIQUE indexes, CHECK CONSISTENCY constraints and other such constraints that SQL manages.

The RFC for the command, SCRUB, can be found here. The progress of it:

• [ ] Scrub checking
  • [x] Add capability to run scrub with AS OF SYSTEM TIME, #20368
  • [X] Add checks for secondary index integrity. #19586
  • [x] Fix performance (when a correctness issue was fixed it began using an extremely slow plan). This will require a special modifier to joins to allow NULL = NULL to be true. #20177
  • [ ] Add checks for SQL constraints
  • [ ] UNIQUE
  • [x] CHECK. #19960
  • [x] FOREIGN KEY. #19960
  • [x] NOT NULL (part of the below physical data errors, #19961)
  • [x] Check encoding errors, and other physical data errors. #19961
  • [ ] System tables (potentially deferred, less high prior.)
  • [ ] Find unexpected/extraneous keys. i.e. an unexpected column family, or keys between table or index spans.
• [ ] Add scrub to testing infrastructure
  • [ ] Look into running scrub during logictests.
  • [x] Add to allocator tests. #20454
  • [x] Add to schema change tests. #20454
  • [ ] Find any additional tests that would benefit from correctness checks.
• [ ] Look into running scrub on a regular schedule on test clusters
• [ ] Adding job controls to scrub
• [ ] Repairing errors
  • [x] Create an RFC on repair strategies. #20293
  • [ ] Add the ability to repair errors

Jira issue: CRDB-6137

Epic CRDB-30356

[petermattis]

Another set of checks to perform is that all of the data associated with a table is correct. For example, we should loop over the rows in the table and verify that all of the associated index keys exists. We should loop over the indexes and verify that we don't have any dangling index keys that aren't referenced by a row. We should verify that the primary index data is organized into column families as per the TableDescriptor (e.g. there is no extraneous column data in a family and there are no extraneous families).

[knz]

@jordanlewis I think this is what we were talking about yesterday ;)

What do you think about making this a starter project?

[vivekmenezes]

I like the list you've come up with. Great job @lego !