sql: version compat between SQL tenant and KV backend

[tbg]

Background

With the introduction of SQL tenant servers, we need a story around upgrades. Initially, this story will be that of the KV layer: adjacent versions can run together as long as the upgrade has not been finalized yet.

However, upgrade finalization at the time of writing uses Gossip, which won't be available to a SQL tenant. Instead, when a tenant server starts, it is told what version to use. This could either happen via configuration (i.e. the supervisor tells it) or by reaching out to the KV layer early in the boot sequence (i.e. before the active version matters), similar to how the KV layer might stop relying on Gossip using #32574.

An upgrade will thus first upgrade the KV layer, finalize that upgrade, and then restart all SQL tenants.

cc @irfansharif for the connection to long-running migrations, where this approach could cause some problems (since I think we are planning to remove the asynchronous component, which makes it illegal to have old SQL servers around while the KV layer is already upgraded).

[tbg]

Actually, for safety we should do the following instead:

• roll kv layer into new binary (don't finalize)
• roll tenants into new binary (use new version)
• finalize update at KV layer

This basically "pretends" that we start the version bump earlier than we actually do, and all tenants receive it before any nodes in the KV layer do.

With long-running migrations, the semantics are going to be more complicated as there's no single "finalize" step. There are two steps, the first of which synchronously tells all (KV) nodes that the new version shall now be used (but which won't have a way to do the same for tenants), after which point any long-running migrations are run; once this is completed the upgrade is finalized (i.e. nodes can theoretically refuse any legacy behaviors, if this is desired).

I suppose it's all the same though, as the first step corresponds to the only step in today's mechanism: before initiating the upgrade, we can roll the tenants into the new version and then start the KV upgrade process. In doing so, we are essentially "pretending" that the KV layer moves the tenants into the new version first, before contacting the rest of the KV layer.

[irfansharif]

+cc @ajwerner, following our discussions around this area. #48436 will also be of interest.

[tbg]

Closing for #48436.