sql: meta-issue to describe remaining fk migration work for 19.2

[jordanlewis]

Essential items that must be completed before 19.2

• restore the old non-schemachanger pathway for constraint schema changes for mixed-version clusters. sql: make schema changes backward compatible with 19.1 #39037
• add support for upgrading and downgrading table descriptors in the remaining 3 cases of backup and restore (for mixed version clusters). backupccl: fix backup/restore after FK table descriptor changes #39757
• write a true mixed-version roachtest that at least exercises making sure that non-downgraded table descriptors (aka true 19.1-synthesized descriptors) can properly be upgraded and so on. We want to take some foreign key descriptors through the entire actual upgrade path as an acceptance / smoke style test, despite relatively high confidence in the testing we've done so far with unit tests and mixed-version simulations in logic tests. roachtest: add upgrade/foreign-key test #42384
• add unit tests for cross-version RESTORE backupccl: add tests for restoring backups from previous versions #39772

Known bugs that only occur in the 19.1/19.2 mixed-version state

• DROP DATABASE CASCADE with a view referencing a table that also has an inbound FK reference doesn't work:

  create database d;
  create table d.t1 (a int primary key);
  create table d.t2 (b int references d.t1);
  create view d.v as select a from d.t1;
  drop database d cascade;
  
  pq: error resolving dependency relation ID 75: error finding foreign key on table 76 for backref table:76 index:2 name:"" validity:Validated shared_prefix_len:0 on_delete:NO_ACTION on_update:NO_ACTION match:SIMPLE
  

  This is probably related to the other pre-existing problem we discovered with corrupted references in dropped tables: sql: old, truncated descriptors can become corrupt. is this a problem? #39498
• The crdb_internal logic tests where we create splits and then run SELECT ... FROM crdb_internal.ranges WHERE split_enforced_until IS NOT NULL are failing for some unknown reason.

Low-priority bugs

• In the mixed-version state,VALIDATE CONSTRAINT hangs when running in the same transaction as another schema change on the same table, likely for a similar reason as in sql: fix deadlock in VALIDATE CONSTRAINT #32772 and sql: VALIDATE CONSTRAINT gets hung up when FK is added all in a single transaction. #38797, where the InternalExecutor tries to acquire a table lease within the user transaction and hangs. (We need to move that validation query out of the user transaction, filed in sql: make VALIDATE CONSTRAINT a schema change job #37712, but that won't work in the 19.1/19.2 mixed version state.)
• The order in which foreign keys are printed in CREATE TABLE statements can differ depending on whether the table descriptor has made a round-trip to disk or not, which causes difficulties for tests. We could fix this by always sorting FKs after we add one, so that they conform to what the printed FK order would have been when they were stored on indexes.

Non-essential reminders and/or improvements

• consider removing validator fields in upgrade and synthesis after 19.2 is finalized.
• consider stripping validator fields from protos during downgrade.

[thoszhang]

fix issues with validate constraint in transactions with other schema changes on the same table by making validate constraint run in the schema changer instead of the user transaction. #37712

I realized that this won't actually fix the problem in the 19.1/19.2 mixed state because moving VALIDATE CONSTRAINT to the schema changer can only happen in the fully upgraded 19.2 state. We can probably figure out how to fix this some other way, but that will have to happen after the beta goes out.

[jordanlewis]

@lucy-zhang I can't remember, is there another issue open for the functionality improvements we wanted to do in 20.1?

[thoszhang]

I don't think so, although there might be some assorted issues for individual things. I'm working on compiling a list now.

[asubiotto]

@jordanlewis Is this meta issue still useful?

[thoszhang]

Yeah, I'm closing this - I don't think the 19.1/19.2 bugs are high-impact enough to fix, and the only lingering bug related to VALIDATE CONSTRAINT is basically filed in #37712 already. There are also some deprecated proto fields that we can get rid of, but that's not hard.