sql: concurrent ADD REGION, DROP REGION, (CREATE|ALTER) REGIONAL BY ROW combinations do not mix

[otan]

Assume that a DROP REGION job fails. If we do not include regions that are being dropped in the SynthesizeRegionConfig during this state, a number of issues arise on REGIONAL BY ROW tables in their given callsites, such as:

• New REGIONAL BY ROW tables created will be missing the dropped region partition.
• alter to/from REGIONAL BY ROW will be missing partitions
• an active CREATE INDEX job on RBR tables may miss the dropped partition
• an active ALTER PRIMARY KEY job on may miss the dropped partition
• an active CREATE UNIQUE CONSTRAINT job will miss the dropped partition
• Partitioning validation is broken during a concurrent ADD/DROP REGION, as per discovery in sql: add validation for partitions matching REGIONAL BY ROW #63459. This is because an add will repartition every table, but will discard the dropped region.

I've tried taking the tack of including DROPPING regions for these operations here in the latter two commits, but unfortunately we hit the error that the enum is not PUBLIC on the repartition case.

We should probably block any REGIONAL BY ROW creating / altering behaviour if there is an ADD/DROP REGION underway (i.e. creating an index, unique constraint, create table ... regional by row, some already handled by #63368) and vice versa - this is the quickest and easiest way to unblock the release. Furthermore, it is probably worth prohibiting concurrent ADD/DROP regions as well - again to unblock the release.

[otan]

cc @arulajmani happy to do this the CREATE INDEX, etc. stuff if you prefer, i'd ask you to do the ADD/DROP concurrency bit because i prefer you to rewrite that test, hehe

[arulajmani]

I think all but the last concern can be accounted for by re-partitioning all Regional By Row Tables even if the Type Schema Change job is rolled back. Concretely, consider the scenario:

• Op 1: Region A is being dropped, but will fail validation in the type schema changer, therefore requiring a rollback.
• Op 2: A RBR table t is being created.

Let's assume these operations are run concurrently. If Op2 is serialized before Op1, then Region A should still be PUBLIC. As such, we should be fine.
The problem, as you pointed out, arises if Op1 is serialized before Op2 and the type schema change job runs and rolls back after Op2. In this case, table t was created without a partition for Region A as it was moved to READ ONLY state by Op1. If validation fails and the operation is rolled back, Region A will be promoted back to PUBLIC. The right thing here would be to re-synthesize a region config here and repartition all RBR tables from this new set of regions. This would mean that t would get a partition for Region A.

You can extend this CREATE RBR scenario to all the other ones enumerated in the issue -- the fundamental problem is the same.

I'm still munching on the last point that pertains to validation.

[arulajmani]

Partitioning validation -- I think we're being too strict here, trying to map out all possible scenarios when enum values are transitioning (READ_ONLY with either DIRECTION_ADD or DIRECTION_REMOVE. I've got a long form explanation on the draft PR #63459 (review), but I think the right answer here is to relax this validation a bit.