Feature Request: Online DDL, in-order completion of migrations

[shlomi-noach]

Feature Description

ApplySchema may run multiple schema changes. With Online DDL, and when --allow-concurrent is given, those migrations may run concurrently. These migrations may then complete at any time of their choosing. A CREATE TABLE migration, for example, can be expected to complete very quickly, as opposed to a long running ALTER TABLE.

Even when --postpone-completion is given, and even if we run ALTER VITESS_MIGRATION COMPLETE ALL when all migrations are ready_to_complete, even then the migrations may complete in any arbitrary order.

We wish to introduce a new DDL strategy flag, --in-order-completion, which enforces the ordering of migration completion. A migration running with this strategy will only complete when it is the first non-pending migration in the same migration context group.

That is, if there's any pending (queued, ready, running) migration whose id is smaller (thus, precedes our migration), our migration will stall and not complete.

Note that it is possible for the migration to run if an earlier migration has failed. It's important to allow that to avoid hanging migrations.

The outcome of this feature is that the order of migrations as submitted in ApplySchema is the order in which the migrations are finalized. The feature puts a specific order to what otherwise would be an arbitrary order.

Tracking issue: #6926

Use Case(s)

The use case for this feature are migrations dependencies. Ideally, an ApplySchema should not have inter-dependencies between migrations, but it can happen.

Examples:

• On a pre-existing table, we add a new column. We also create a VIEW that selects from that column. In this situation, the ALTER TABLE must fully complete before we attempt to create the view, even though view creation is near instantaneous.
• Creating two tables with parent-child foreign key relationship. The parent table must be created first, the child table second.

With --in-order-completion, we assume the user has sorted out the correct order of migrations. vitess does not attempt to order the migrations. The task is very complex and sometimes not at all possible. Possibly omething for the future, but not required for now. schemadiff is able to resolve some ordering dependencies.

[shlomi-noach]

Originally I thought the feature should be scoped to migrations in the same migration context. But I would also like to explore the possibility of cross-context in-order completion. This will be useful if, for example, you submit a single migration at a time, and still want them to run concurrently and complete in-order. Even more interesting is when you want to REVERT multiple migrations, concurrently, and want to complete the reverts in-order.