CockroachDB locality changes must preserve region dependencies

A table can depend on a type even when the type's own metadata does not record the relationship. That incomplete dependency can remain unnoticed until another schema operation asks whether the type is still in use. CockroachDB advisory A-84144 describes this sequence for tables converted to regional-by-row locality.

The July 19 advisory concerns CockroachDB 22.1.0 through 22.1.3. Converting an existing multi-region table to REGIONAL BY ROW could omit a back-reference from the region enum type to the table. A subsequent region drop could then succeed when it should have failed. Later changes to the table locality could fail while decoding data that still used the removed region value.

I accept CockroachDB's coordination costs because explicit regional placement and consistency are useful together. This defect concerns the schema metadata that lets those regional choices remain safe over time. A locality option needs to preserve dependencies as well as move the table toward its requested arrangement.

Conversion creates a dependency that must be recorded twice

The advisory's reproduction starts with a multi-region database and a table whose locality is regional by table in the primary region. It then converts the table to regional by row and inserts rows assigned to two regions. At this point, row data uses the region enum to represent its regional assignment.

The table's use of that type is one direction of the relationship. The type descriptor's reference to the table is the other. A schema operation inspecting the type needs the reverse direction to identify which tables might prevent a change.

The conversion repair in pull request 84339 explains the missing step. Changing an existing table to regional by row failed to add the table to the region enum's back-references. The table depended on the enum, while the enum was unaware of that dependent table.

This is a metadata inconsistency even before the later destructive operation. The visible table definition can look like the requested locality, and inserts can use the regional value. Neither observation establishes that another schema command will discover the dependency when it needs to enforce a restriction.

Dropping a used region should fail

In the advisory's example, a row belongs to us-west1. Dropping that region from the database should produce an error because the table still uses it. The affected implementation instead allows the drop. The missing reverse reference keeps the type-level operation from accounting for the converted table.

The next attempted locality change, to global, fails during index backfill. Its error says the region enum cannot find the physical representation associated with the removed value. The remaining data refers to a value that the current type descriptor no longer knows how to interpret.

That sequence separates the operation introducing the inconsistency from the operation exposing its consequence. Conversion omits metadata. Region removal then makes that omission destructive. A later backfill encounters the damaged relationship while trying to construct index entries.

It would therefore be incomplete to describe the advisory as an ordinary failure of ALTER TABLE ... SET LOCALITY GLOBAL. That statement is where the example reports the decoding error, but the earlier region drop should never have been permitted. Repairing only the last operation would leave the incorrect dependency decision unexplained.

The issue is also narrower than saying every regional-by-row table is necessarily corrupt. Its advisory identifies tables whose locality was altered to that setting in the affected versions, followed by the problematic region operation. The conversion history is part of the exposure described by the source. Inspecting only the current locality omits that history. Two tables can display the same locality while having reached it through different schema operations, which is why the conversion path appears in the advisory's conditions.

New conversions need the missing back-reference

The first patch adds a call that records all newly created type back-references during the conversion. It places that work after creating the relevant table metadata and before proceeding through the remaining mutation arrangement. The repaired operation establishes the dependency information that later type changes need.

A regression test follows the advisory's sequence. It creates a regional-by-table table, converts it to regional by row, and inserts regional rows. Dropping a region still used by a row must fail with a dependency error. The test then distinguishes this from a separate table arrangement where dropping an unused region is allowed.

That distinction matters because the desired behavior is not a universal prohibition on changing database regions. The operation needs enough dependency information to reject a region still in use. A valid removal should remain available when no relevant row requires the value.

The patch description explicitly says this first fix does not help existing clusters that already ran the faulty conversion. Once the missing metadata has been stored, correcting the conversion function does not revisit every earlier table. A complete maintenance response needs to handle that preexisting state.

I find this explicit separation useful. A schema bug can require both a corrected writer of metadata and a way to account for metadata written by the old code. Treating the first patch as sufficient would protect only conversions that happen after installation.

Existing metadata is checked before region removal

The second repair in pull request 84349 repairs missing table back-references before DROP REGION or DROP SUPER REGION. It is specifically included on the 22.1 release branch. The advisory identifies both patches as part of the fix in 22.1.4.

The repair collects table and type descriptors belonging to the database. For each table that has not been dropped, it obtains the set of referenced type identifiers. It then checks whether each referenced type already lists the table among its referencing descriptors.

When a reverse reference is absent, the code obtains a mutable type descriptor, adds the referencing table identifier, and writes the updated descriptor. The subsequent region operation can then use the corrected metadata. The check reconstructs the reverse relation from the forward relation already expressed by the table.

This approach places the repair before the operation whose safety depends on the missing information. It does not require a future locality conversion to happen before the check runs. An already converted table can receive the missing back-reference when an administrator attempts the region change.

The method also explains what information makes the repair possible. Each table descriptor still records the forward dependency. The code can derive the reverse dependency without inventing a new association from row contents alone. It is restoring consistency between two catalog representations of the same relationship.

Metadata repair is not a promise to recover removed values

The advisory recommends updating affected multi-region clusters to 22.1.4 or later. Its documented repairs prevent missing references on new conversions and restore old references before further region removals. Those are concrete protections against the sequence it describes.

The sources do not establish a general automatic repair for a table whose required enum value has already been dropped. Reconstructing a back-reference and restoring a removed type value are different operations. A post-update dependency check should not be described as a universal reconstruction of already damaged data.

The original public issue reports decoding failures during a locality change and supplies a stack trace through the backfill machinery. That evidence helps connect the visible error to reading enum values. The advisory's controlled reproduction and the patches provide the more specific causal account of the missing dependency and unsafe region drop.

This distinction keeps diagnosis tied to the operation history. A decoding error during a backfill is a symptom with several contextual facts around it. The repaired metadata path identifies the particular dependency failure addressed by this advisory, rather than treating every internal backfill error as the same problem.

Regional schema changes depend on catalog consistency

The conversion command changes more than a display property in a table definition. It introduces use of the region enum and therefore changes which future database operations are valid. The reverse references are part of enforcing that consequence.

The two patches divide the work according to when the metadata was created. New locality conversions record their dependencies correctly. Existing converted tables get their reverse references restored before a region-removal decision relies on them. Together, those changes let the database reject a removal that would leave row data referring to an enum value the type no longer contains.