CockroachDB imports need a global uniqueness check

A unique physical index key does not always prove the uniqueness rule written in SQL. Regional placement can add implicit key information, allowing two physically distinct entries to represent a duplicate logical value. A bulk import must account for that difference instead of relying on the index representation alone.

The April 12 advisory A-79281 describes IMPORT INTO accepting duplicate keys in REGIONAL BY ROW tables in multi-region databases. The violated constraint can be a primary key as well as another unique constraint. CockroachDB 21.2.0 through 21.2.7 are affected, along with the listed early 22.1 prereleases. Version 21.2.8 contains the maintenance repair.

I like CockroachDB's explicit treatment of regional data placement. This case shows why placement and SQL constraints must remain connected through every write path. A data-loading operation needs to enforce the same logical uniqueness that an application expects from ordinary writes.

The reproduction contains only one imported duplicate

The original issue 79281, filed April 2, creates a regional-by-row table with an integer primary key. It inserts an existing row assigned to one region, then imports a CSV row. The import reports success. A subsequent query shows the primary-key value twice.

The important detail is not an unusually large import or a complicated composite business key. One imported row can be enough when the existing row and the imported representation occupy different regional positions. The logical identifier is supposed to remain unique across the table.

That makes the symptom different from a stale secondary lookup. The query displays duplicate logical values admitted by the load. Repairing future import behavior does not by itself choose which existing duplicate represents the intended application record.

The report explicitly connects the missing check to validation already needed after index backfill. That comparison points toward the general issue. A bulk operation that builds physical data directly still needs to enforce constraints that are not completely represented by the physical index key.

Implicit regional keys change what the index proves

The repair in pull request 79293, merged April 4, identifies implicitly partitioned unique indexes and UNIQUE WITHOUT INDEX constraints as requiring additional validation. Its helper determines whether a table has these virtual uniqueness constraints before reporting a revalidation phase.

For an implicitly partitioned index, the validation code selects the logical key columns after the implicit partitioning columns. That is the relevant distinction between regional physical organization and the uniqueness rule visible in the table definition. Two entries can differ in the implicit prefix while repeating the columns whose combination must be unique to the application.

The patch does not claim that every index needs a new identical check. It selects constraints that cannot rely on the underlying index alone. That targeting explains why the failure appears with regional-by-row tables even though unique indexes ordinarily sound sufficient to reject duplicate values.

It also explains why a primary key can be involved. Calling a SQL constraint a primary key does not remove the implementation work needed to preserve it across implicit regional partitioning. The import path has to validate the logical columns corresponding to that rule.

Validation becomes part of import completion

The patch adds a post-import check before the job proceeds through its remaining completion work. For each imported table, it builds a usable descriptor and invokes the constraint validation machinery in a transaction. If validation fails, the import fails and rolls back, according to the patch's release note.

A regression test makes the regional distinction explicit. It puts an existing primary-key value in one region and imports the same value for another. The expected outcome is a failure to validate the unique constraint. The test therefore covers the logical collision that physically separate regional entries could previously admit.

This is a stronger completion condition than finishing ingestion without a low-level key collision. A successful load must satisfy the declared table constraints after its rows have been placed. The validation phase provides the missing check for the selected constraint types.

I find the job-level repair appropriate to the mechanism. The fast load path can retain its bulk behavior while adding the semantic validation that its representation requires. The point at which the job reports success then reflects the logical constraints rather than ingestion alone.

Existing imports need an audit

The advisory directs affected users who used IMPORT INTO to audit regional-by-row tables with unique constraints and to upgrade. Those instructions address different states. The new code rejects a bad future import, while the audit finds duplicate data that a previous import already accepted.

Versions 21.2.6 and later include built-in revalidation functions. The advisory supplies functions for every table, one table, or one named constraint. For a table called orders, the table-level form is this.

SELECT crdb_internal.revalidate_unique_constraints_in_table(orders);

A validation failure returns an error with a hint identifying the data causing the violation. The advisory then calls for manually updating or deleting offending rows. That manual step reflects a real ambiguity. The database can detect that two rows violate a rule, but the advisory cannot determine which application record the user intends to preserve.

For versions before 21.2.6, the advisory describes grouping by the columns of each affected uniqueness rule and checking for groups with more than one row. The audit has to follow each relevant logical constraint. Looking only for duplicates in one convenient identifier can miss another unique combination.

Upgrading does not resolve business identity conflicts

The impact statement includes 22.1 alpha releases through alpha 5 and beta 1 as well as the 21.2 production versions. That makes this a concern for prerelease evaluations using the affected import path too. The issue is defined by the operation and table arrangement, not simply by whether a deployment is called production.

The concrete repair is post-import validation followed by failure and rollback when it finds a violation. Existing duplicate rows require a separate decision about their intended contents. That division keeps the implementation fix and the application data correction understandable as separate parts of recovering from an import that should have failed.