sql: improvements to constraint validation

[thoszhang]

This issue is for tracking all work related to #32118. The overall goal is for constraint validation on existing rows of a table (which can take a long time) to happen in a separate transaction from the user transaction of the ALTER TABLE query, by running the validation through the schema changer. As part of the change, the validation of existing rows will occur when the constraint is added (which is what Postgres does by default), instead of when VALIDATE CONSTRAINT is run.

This will be done by adding a new type of mutation, representing a constraint to be validated, and a new ConstraintValidity state. When ALTER TABLE ... ADD CONSTRAINT is run, a constraint is added to the table descriptor (as before), but in the (new) Validating state, and will be visible to CRUD operations. A mutation for validating the new constraint is also added to the mutations list. The validation for existing rows then happens during the backfill step; if it succeeds, then the state of the constraint on the table descriptor will be updated to Validated, and if it fails, the constraint will be dropped.

The validation itself should use a backfiller, but in the short term can also plug in to the existing validation implementations that use SQL queries. A backfiller for check constraints has already been implemented by Erik in #32504, but it's not high-priority for FK constraints.

A planned list of changes/PRs is as follows:

• Make changes to check constraints
  • (Very minor) Slightly refactor existing check constraint validation implementation, which uses a SQL query, to be independent of planner
  • Make the new validation mutation changes, as described above
  • Use Erik's backfiller implementation instead of the SQL query (this could be done in the previous steps, but it makes sense to do it separately to keep the PRs smaller) (We'd do this based on need, but there's no indication that we'd need to yet)
• Make changes to FK constraints
  • (As above) slightly refactor the existing FK constraint validation implementation
  • Fix sql: validating FK constraint uses extremely slow hash join #33452 (rewrite the SQL query to use a merge join)
  • Make the new validation mutation changes, as described above (most of the work should have been done already for check constraints)
  • (Future) Implement FK validation using a backfiller (We'd do this based on need, but there's no indication that we'd need to yet)
• (Future) support NOT VALID (sql: support ADD CONSTRAINT ... NOT VALID #33551) and update VALIDATE CONSTRAINT to be in line with the above changes
  • The plan is to have ADD CONSTRAINT add the constraint in the Unvalidated state, and for VALIDATE CONSTRAINT to add a mutation to validate it; if the validation fails, the constraint is not dropped but remains Unvalidated, which is how Postgres does it

[vivekmenezes]

@lucy-zhang this looks like a good plan. parts 1 and 2 look good. I'd move part 1c: implement erik's backfiller for future. I'd like to motivate that by create a large table and proving that chunking is needed for better progress reporting and schema lease extensions.

[thoszhang]

I realized that a problem with using SQL queries for validation is that we can't run queries on non-public columns, so we won't be able to add constraints on columns that are being added. So we do actually need the backfiller to preserve that functionality.

I opened #34301, which implements the check constraint validation changes with Erik's backfiller implementation.

[dt]

Everything that isn't tracked in a separate issue is done here