sql/schemachanger/scbuild: ensure proper version gating for mixed-version state

[ajwerner]

Is your feature request related to a problem? Please describe.

We ought to produce the same elements in the mixed version state as we do in the old version. That's how we'll ensure compatibility. We need to both do that and to validate that we do that.

Describe the solution you'd like

We need to find a way to ensure that the same statement and initial state result in the same exact state protobufs between the previous version and the current version. We could do this in a variety of ways, including writing "golden" protos on a per-release basis or finding a way to link old code directly.

Describe alternatives you've considered
There might be some way to do this as an integration test. I don't know that that would be better.

Jira issue: CRDB-15895

[blathers-crl]

Hi @ajwerner, please add branch-* labels to identify which branch(es) this release-blocker affects.

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

[blathers-crl]

Hi @ajwerner, please add branch-* labels to identify which branch(es) this release-blocker affects.

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

[ajwerner]

The issue is a bit more involved than what we need up front. The initial task here is to just mark the statements and alter for which commands for which we added support in the new release and to gate them on cluster version activation. The secondary tasks relate to testing. Let's do the gating in an initial PR and think about testing for a second PR.

[Xiang-Gu]

The merged PR was only part 1/2 towards ensuring backward compatibility in mixed version state for declarative schema changer.

As part 2/2, I have written a roachtest that aims to test declarative schema changer jobs created by nodes running in the newer version should be able to be adopted and finished by nodes running in the older version. That PR is currently blocked because of known issues (e.g. OFFLINE status is not backward compatible so Andrew is working on a PR that removes it and brings TXN_DROPPED status back).

I'm going to reopen this issue, and it should be closed once that roachtest passes, which means we fixed all the backward incompatibility.

[ajwerner]

The basic part of this is done in #86604, we'll downgrade to GA blocker to wait for roachtest in #86950