sql/schemachanger: Secondary indexes and PK are inconsistent during ALTER PK

[spilchen]

Describe the problem

There is an issue when altering the primary key (PK) to replace the rowid column with a user-defined column. During this schema change, a transient version of the PK is created before reaching the final version: initially, both the new user column and the rowid coexist in the PK, and eventually, the final PK has only the user column, with rowid removed. However, if the table has secondary indexes, these aren't rebuilt until the final PK version is established.

This creates a problem when queries using the secondary index are run during the schema change. If a query references columns that aren't included in the secondary index but should be based on the new PK columns, the query will fail, reporting that the requested column is missing from the index. The root of the issue is that the old secondary indexes are being used alongside the new PK during the schema change.

Potential solutions include:

• Rebuild the secondary indexes multiple times in sync with each PK change, ensuring consistency between the PK and secondaries at each stage.
• Enhance the optimizer to detect this mismatch and force a query retry.
• Avoid creating multiple versions of the PK during this operation and transition directly to the final version. This would require enforcing a rule that prevents altering the PK in combination with other operations in the same ALTER statement.

This problem has existed since at least 23.1.

To Reproduce

The test was initially found when TestExplainGist would fail occasionally. See #130282.

Jira issue: CRDB-43464

[blathers-crl]

Hi @spilchen, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[spilchen]

We may not be able to transition from the old to the new index. See this slack conversion to understand why the transient PK is needed: https://cockroachlabs.slack.com/archives/C04N0AS14CT/p1677706410017719