CockroachDB repairs UPSERTs that stopped after ten thousand rows

A successful SQL response should not conceal that the executor ignored the rest of its input. CockroachDB's September 24 advisory A-54418 describes a regression affecting UPSERT statements with more than 10,000 input rows. Versions 20.1.4 and 20.1.5 could stop after that threshold without a visible error and report an affected-row count of zero. The threshold concerns rows in a statement, rather than the number of statements a client sends.

The advisory also covers INSERT with the corresponding ON CONFLICT update form. It recommends upgrading affected deployments to 20.1.6. Installations avoiding those affected releases can instead keep each UPSERT statement at or below the documented row limit. Earlier maintained branches and 20.1.0 through 20.1.3 are not affected by this regression.

I value CockroachDB's serializable default, but isolation and complete statement execution are separate obligations. A transaction can be ordered consistently with other transactions while its executor still mishandles how much input it processes. This defect concerns that second obligation. The application asked for a larger operation than the executor actually completed.

Internal batches are not client statements

Yahor Yuzefovich's repair patch explains how an earlier correction introduced the regression. A counter tracking UPSERT results could become inconsistent with the rows retained for RETURNING. The earlier change reset the counter, but did so unconditionally. The executor also used a nonzero counter to indicate that batched processing had more work to deliver.

Consequently, resetting the counter could signal completion after the first internal batch even though the statement still had input rows. The application had not requested an operation limited to that batch. Batching was an implementation detail, and the completion signal incorrectly made that internal boundary visible as silent truncation of the requested work.

The repair introduces a field describing rows in the last processed batch and uses it for the batch result. It also moves clearing of RETURNING rows until the next batch begins. Those changes preserve results long enough for their consumer to read them while keeping the continuation decision associated with the batch that actually ran.

UPSERT resolves primary-key conflicts

The 20.1 UPSERT reference available at disclosure describes inserting when the primary key does not conflict and updating when it does. UPSERT considers primary-key uniqueness. INSERT ON CONFLICT can specify other conflict targets, so the syntax forms are related without being interchangeable in every application.

For an existing table with id as its primary key, a small illustrative statement is:

UPSERT INTO account_status (id, status)
VALUES (1, 'active'), (2, 'paused');

The advisory's problem appears when one statement processes more than 10,000 rows, not in this two-row example. Its significance for bulk loading comes from the normal usefulness of multi-row operations. An application may deliberately issue one large statement to avoid repeated client round trips, while the server internally processes that statement in smaller units.

The required privileges remain INSERT, SELECT and UPDATE on the table. Those permissions establish that the application may perform the operation. They do not change the executor's obligation to process its full input. A repair to internal batch accounting is necessary even when every requested row is authorized and every primary-key conflict has a valid update interpretation.

RETURNING had a separate visible consequence

The 20.1.6 release notes describe large UPSERTs with RETURNING processing up to 10,000 rows but returning none. That is more specific than interpreting the advisory's at-most-10,000 returned-row description as evidence that every affected statement returned exactly its processed rows. Both accounts identify the same unsafe early termination, while the release notes explain the observed empty result.

This distinction matters to application logic that consumes returned identifiers or values. An empty result can appear to describe an operation with no matching output, even though some writes occurred. The reported count is also unreliable under the defect. A consumer therefore cannot use that count or RETURNING result alone as evidence that the intended input was fully applied.

I regard the absence of a visible error as the defining operational problem. A retry path usually begins with a failure signal. Here, the caller may have accepted the response and moved on, leaving the intended tail of the input absent. Updating prevents future execution through the faulty path, but an earlier successful-looking response still needs to be understood according to the affected version's behaviour.

The regression test crosses the internal boundary

The patch's regression test deliberately uses more than one internal batch. It inserts 25,000 generated rows through an UPSERT and then checks the stored count. A second operation uses RETURNING in a common table expression and checks that 15,000 returned rows are counted. The two assertions cover stored effects and result consumption separately.

The common table expression documentation explains the relevant construction. A data-modifying statement can be a CTE when the WITH clause is at the top level. Its results can then feed the outer query. That gives a regression test a direct way to count the rows exposed by RETURNING without relying on a client's presentation of an affected-row status.

The important property of those tests is crossing the batching boundary. A two-row UPSERT can exercise syntax and conflict resolution while missing this regression entirely. The repair needs evidence that execution continues beyond the first internal batch and that its returned rows remain available to the next consumer. Those are the contracts the faulty counter reset broke.

For an affected application, the corresponding unit of investigation is the individual bulk statement. A client transaction containing many small UPSERT statements has a different relationship to the threshold from one UPSERT fed by a large SELECT. Identifying the statement's actual input size makes the advisory actionable and avoids treating a generic client batch-size setting as equivalent to the executor's internal batch boundary.