Unique index inconsistency after primary-key updates on rows with NULL key columns

Description

On affected versions, updating the primary key of a row can leave a unique secondary index inconsistent with its base table, but only under a specific combination of conditions. Only rows that meet all of them are at risk. After an inconsistency is introduced, queries served by the index can silently return wrong results, and inserts can fail with duplicate-key errors for keys that do not exist in the table.

The base table data on disk remains correct throughout. The damage is to the index entry, which is left at a stale position that no longer matches the row it describes.

All of the following conditions must hold for the problem to occur:

• yb_enable_inplace_index_update is enabled. This is the default.

• The index is a unique secondary index using NULLS DISTINCT semantics. This is the default for CREATE UNIQUE INDEX. Indexes created with NULLS NOT DISTINCT are not affected.

• The index key columns do not include the primary key of the base table. For a base table with primary key colPK, an index in the form CREATE UNIQUE INDEX ON tbl (colPK, colA) is not affected.

• At least one of the index's key columns is NULL in the affected row. For example, given CREATE UNIQUE INDEX ON tbl (colA, colB) INCLUDE (colC, colD), a row with NULL in colA or colB is at risk; a row with NULL only in colC or colD is not.

• An UPDATE changes the row's primary key without changing any of the index's own key columns. This introduces a latent inconsistency, with impacts to query results appearing only after the second update to the key. This will be flagged in yb_index_check() when it is run.

• A second UPDATE which changes the same row's primary key in the same manner (that is, without changing any of the index's key columns) produces the following observable symptoms.

  • Spurious duplicate-key errors. After the second primary key update as described above, any operation that would insert a row using the original primary key value is rejected with a duplicate key violation, even though no row with that key exists.
  • Missing rows from index scans. After the second primary key update, an index scan using the affected index silently omits the row. For example, a WHERE v IS NULL predicate satisfied by the index returns no row, while a sequential scan of the same table returns it. No error is reported.

Mitigation

Upgrade to a release that contains the fix (see the above table).

If an immediate upgrade is not possible, disable in-place index updates by setting the following YB-TServer flag:

--ysql_pg_conf_csv=yb_enable_inplace_index_update=false

Disabling this flag forces index maintenance to use delete-and-reinsert, which avoids the bug. Updates that previously modified only a covering index's INCLUDE columns will no longer take the in-place path, which can reduce update performance for those queries, but index correctness is preserved.

To find unique indexes that may already be inconsistent, use the yb_index_check() function. If it reports an index as inconsistent (in this case, showing a mismatch between ybuniqueidxkeysuffix and ybbasectid), drop and recreate that index after setting yb_enable_inplace_index_update to false, so the rebuilt index is not corrupted again.

Details

A unique index must allow multiple rows with NULL in the indexed column to coexist without key collisions. YugabyteDB handles this with a system-managed key column derived from the primary key, which makes each NULL row's index entry distinct.

The yb_enable_inplace_index_update optimization skips the delete-and-reinsert cycle when an UPDATE changes only non-key index columns, updating the existing entry in place instead. The logic that decides whether in-place is safe examined user-visible key columns but did not examine this system-managed column. Because it is derived from the primary key, a primary key update on a row with a NULL-indexed column effectively changes a key column of the index without the logic detecting it.

This produces two observable failure states as updates accumulate, both of which are only present for operations making use of the affected index and rows, as the base table remains correct.

After one primary-key update, query results are still correct. The inconsistency is latent and only detectable via yb_index_check(), which will report a "mismatch between ybuniqueidxkeysuffix and ybbasectid."

After a second primary-key update, the in-place write targets a key position that does not exist in the index. This produces the observable symptoms: missing rows from index scans and spurious duplicate-key errors.

The fix ( #32126) adds the missing check so that a primary-key update on a NULL-indexed row correctly triggers delete-and-reinsert.