This PR changes Nexus migrations from "execute DDL step and immediately move on" to "execute DDL step, then run a generated verification query, and only then advance migration state/version." The reason is #9866/#9888: for backfill-prone DDL, Cockroach can acknowledge the statement before asynchronous schema work has fully completed.

The rest of this comment argues that the verification predicates used by the PR are correct — that they become true only after Cockroach has actually finished the relevant async schema-change work, making them valid barriers before Nexus advances migration state. The argument is against release-22.1-oxide; all code links below are permalinks to the current tip of that branch.

Why CREATE INDEX IF NOT EXISTS needs a barrier

CockroachDB schema changes are asynchronous. When you run CREATE INDEX, Cockroach adds the index to the table descriptor as a mutation (an in-progress schema change) and returns. The actual work — backfilling data into the index, validating uniqueness constraints — happens asynchronously in the schema changer.

The problem is that IF NOT EXISTS is too permissive about what counts as "exists." In create_index.go#L659-L669, CREATE INDEX calls FindIndexWithName, which searches with AddMutations: true and DropMutations: true (table_desc.go#L361-L366). If it finds a match — even an index that's mid-backfill — IF NOT EXISTS returns success immediately. So a second Nexus replica running the same migration can get "success" before the index is actually usable.

Why crdb_internal.table_indexes is a correct barrier

The PR verifies index creation by checking crdb_internal.table_indexes. This virtual table is stricter than IF NOT EXISTS: it iterates indexes via catalog.ForEachIndex with default IndexOpts (crdb_internal.go#L2751-L2764), which excludes mutations. ForEachIndex filters out any index where Adding() or Dropped() is true unless you opt in (table_elements.go#L532-L540). So crdb_internal.table_indexes shows only public indexes — those that have graduated from mutation status.

The remaining question is whether an index becomes public only after the backfill and validation work is actually done. It does. The schema changer's lifecycle runs in three phases (schema_changer.go#L1959-L1971): first advance mutation state, then runBackfill (which includes data backfill, merge from a temporary index that captured concurrent writes, and validateIndexes — backfill.go#L1931-L1940, backfill.go#L2072-L2073), and only if that succeeds, done(). If runBackfill errors, done() is never called and the index stays in mutation state.

done() is the function that actually makes the index public: it calls MakeMutationComplete for each mutation (schema_changer.go#L1588), which for index-add mutations calls AddSecondaryIndex to insert the index into the public index list (structured.go#L1613-L1623), then trims the mutation from the descriptor (schema_changer.go#L1729-L1731).

So the chain is: an index appears in crdb_internal.table_indexes only if it's public, it becomes public only in done(), and done() runs only after backfill and validation succeed. Checking this table is a valid barrier for the IF NOT EXISTS race.

Why SHOW CONSTRAINTS ... validated = true is a correct barrier for ADD CONSTRAINT

The same pattern applies to constraint creation. The PR verifies ADD CONSTRAINT by checking SHOW CONSTRAINTS for validated = true.

SHOW CONSTRAINTS projects pg_constraint.convalidated as the validated column (show_table.go#L196-L210). convalidated is set to !con.Unvalidated (pg_catalog.go#L1006-L1014). And the constraint detail code marks any constraint whose Validity is not Validated — including Validating — as Unvalidated (table.go#L360-L362, table.go#L382-L384, table.go#L418-L420). So validated = true requires Validity == Validated, and a constraint that's still in the Validating phase reads as validated = false.

The transition to Validated happens in the same done() → MakeMutationComplete path: the function finds constraints in Validating state and flips them to Validated (structured.go#L1629-L1634, structured.go#L1646-L1652, structured.go#L1664-L1670). As with indexes, done() only runs after runBackfill — which includes validateConstraints (backfill.go#L341-L360) — completes successfully.

So for both verification forms used by the PR, the predicate becoming true corresponds to a post-condition stronger than "DDL statement returned OK" — it means completion in the schema changer's state machine sense, which is the property needed to avoid the #9866 / #9888 class of false-progress migration outcomes.

Safety vs. liveness under descriptor caching

The verification query may run on a different CRDB node than the one that executed the DDL. Descriptor lease/cache lag on other nodes can produce temporary false negatives, where a verification query doesn't yet see the now-public index or validated constraint. This affects liveness (Nexus retries the verification) but not safety (Nexus never advances migration state prematurely). The dangerous direction would be a false positive — seeing a public index before backfill completes — which the argument above rules out: an index or constraint can only appear as public after done() atomically commits the updated descriptor, and no stale cache can conjure a public index that was never written.

Scope and caveats

This argument covers the legacy schema changer job path. Our fork defaults use_declarative_schema_changer to on (exec_util.go#L477-L489), which means the declarative path is used for "supported statements in implicit transactions" and falls back to legacy otherwise. Nexus runs migration DDL inside explicit transactions (via transaction_retry_wrapper), so the legacy schema changer is always the active path for these statements. The declarative schema changer uses different code paths and state machines; this comment doesn't address it, but it also isn't exercised here. There are also other MakeMutationComplete call sites for scenarios like in-transaction schema changes on newly created tables and TRUNCATE on empty tables, but those aren't the async existing-table path behind #9866.

One concern worth addressing explicitly: could there be a window during done() where the index has been removed from the mutations list but not yet added to the public list (or vice versa), creating a moment where a verification query sees an inconsistent descriptor? No — done() modifies the descriptor in-memory (both MakeMutationComplete and trimming mutation entries), then writes and commits it in a single transaction via WriteDescToBatch + txn.Run (schema_changer.go#L1729-L1731, schema_changer.go#L1852-L1856). External readers see either the pre-commit descriptor (index still a mutation) or the post-commit descriptor (index public, mutation trimmed), never a half-state.

Finally, this proof covers the two verification predicates the PR actually uses: crdb_internal.table_indexes for index existence and SHOW CONSTRAINTS ... validated = true for constraint validation. If future work adds verification for other backfill-prone DDL types, each new predicate would need its own argument.