CockroachDB 2.0.4 checks constraints on conflict updates

An upsert contains an insertion path and an update path, and both must preserve a table's constraints. Checking the proposed insert does not establish that a different value assigned by the conflict action is valid. CockroachDB 2.0.4 fixes a missing CHECK-constraint validation on that update path.

The July 16 release notes state the change precisely. CHECK constraints now apply when INSERT ... ON CONFLICT DO UPDATE updates a conflicting row. This matters because the SQL statement can propose an acceptable insertion value while assigning an unacceptable update value. The branch actually taken determines which resulting row needs validation.

I value CockroachDB's strong transaction semantics, but isolation and application invariants answer different questions. Serializable execution cannot repair a missing constraint check. A transaction can occupy a valid place in an execution order while writing a row the schema should have rejected. Both properties need their own enforcement.

The conflict action produces a new row

The patch in pull request 26699 adds validation in the table upserter before it queues the update in the key-value layer. It loads the update values into the constraint helper and checks the expressions. That placement matters because the constraint must describe the values the update will store.

The regression test begins with a valid row and then attempts an invalid conflict update. A small version of that case is:

CREATE TABLE readings (
  id INT PRIMARY KEY,
  value INT,
  CHECK (value < 1)
);

INSERT INTO readings VALUES (1, 0);

INSERT INTO readings VALUES (1, 0)
ON CONFLICT (id) DO UPDATE SET value = 2;

On the fixed path, the last statement reports a CHECK-constraint failure. Its proposed insertion has value zero, which satisfies the condition. The conflict action assigns two, which does not. This is why validating only the insertion candidate cannot enforce the invariant for the complete statement.

The patch also tests valid conflict updates, multiple CHECK expressions and different insertion-column orders. These cases matter because the row being checked needs the correct mapping between column identities and values. A validation call that inspects the wrong position could look correct for one column order and fail for another.

Checking multiple expressions also avoids treating the first constraint as representative of the entire row. An update may satisfy one condition while violating another. The database needs to reject the row on any applicable failing condition. The tests make those independent failure cases visible.

For a client, the expected failure is part of the statement's result. A rejected update lets the application report that the requested value violates the schema. Silently accepting it changes what subsequent readers can assume about the table. This is especially consequential when downstream code omits a duplicate validation because it trusts the declared constraint. The corrected update path restores the database's responsibility for that decision, at the place where it has the final values available.

A schema promise must survive every write path

Applications use constraints to make an invariant apply regardless of which client changes the data. If one statement path omits enforcement, moving validation into application code cannot restore that universal property. Another client or a future code path can still use the affected statement. The database implementation needs the check at the point where it accepts the resulting row.

The distinction also changes how a maintenance release affects existing data. Correcting future writes does not logically prove that every row written through the older path already satisfies the condition. The release note describes enforcement of the operation. Any assessment of prior rows needs evidence about which statements ran and which values they produced.

My preference is to keep that claim narrow. The fix establishes that the affected update branch now checks the constraints. It does not establish a history of application behavior, and it should not be presented as a repair of rows that may already exist. Operators can relate the defect to their actual use of conflict updates without assuming every table is affected.

Decommissioning also gets a more accurate condition

The same release corrects replica counting on down nodes during decommissioning. The notes say --wait=all no longer waits indefinitely because of that counting error, and the separate --wait=live option becomes unnecessary and deprecated. This is another case where an administrative decision depends on representing the relevant state accurately.

An operator waiting for decommissioning needs the command's completion condition to correspond to actual replica progress. Incorrectly counted replicas can keep that condition false even after the intended work should permit completion. The release fixes the stated accounting problem instead of asking the operator to select a weaker waiting mode.

These fixes affect different operations, but both are concrete enough to assess without a general stability claim. The SQL change validates the row produced by a conflict update before queuing it for storage. The decommissioning change corrects the replica count used by the waiting command. Version 2.0.4 therefore improves both the enforcement of a declared row invariant and the interpretation of progress while removing a node.