Index inconsistency with in-place update on INCLUDE column

Description

Concurrent updates to columns that are being added to an index using the INCLUDE clause, and only those columns, while the index is being created can lead to inconsistencies in the newly created index.

The following conditions need to be true for the problem to occur:

• A CREATE INDEX operation is in progress.
• The index has included columns.
• A concurrent UPDATE operation modifies the included columns of the index, but does not modify any other columns that are part of the index.

Root cause

A CREATE INDEX operation involves two main steps:

1. Index row creation for existing data: Index rows are written for all rows that currently exist in the table.
2. Concurrent write handling: Other sessions write to the index concurrently while the index is being created.

When only the included columns are updated, YSQL updates the index row in place (instead of deleting and recreating it). However, if a concurrent UPDATE runs before CREATE INDEX has physically written that row to the new index, then the UPDATE operation will not find a row to modify, and the update will be ignored.

Mitigation

Identify and fix inconsistent indexes

1. Find indexes with included columns:

   SELECT
     n.nspname AS schema_name,
     t.relname AS table_name,
     i.relname AS index_name,
     pg_get_indexdef(i.oid) AS index_definition
   FROM pg_index ix
   JOIN pg_class i ON i.oid = ix.indexrelid
   JOIN pg_class t ON t.oid = ix.indrelid
   JOIN pg_namespace n ON n.oid = t.relnamespace
   WHERE ix.indnatts > ix.indnkeyatts  -- indexes with included columns
     AND ix.indisprimary = false       -- secondary indexes only
   ORDER BY schema_name, table_name, index_name;
   

2. Check existing indexes for inconsistency using the yb_index_check() function.

   If yb_index_check() reports any index as inconsistent, drop and recreate the affected index. Set the yb_enable_inplace_index_update configuration parameter to false before recreating the index so that the issue is avoided on the newly created index.

Prevent future index inconsistencies

Disable the in-place update feature by setting the yb_enable_inplace_index_update configuration parameter to false using the ysql_pg_conf_csv YB-TServer flag. For example:

--ysql_pg_conf_csv=yb_enable_inplace_index_update=false

This avoids the index consistency issue. Note that disabling the feature can degrade performance for queries that update only the INCLUDE columns in the index.

You can also upgrade to a YugabyteDB version that contains the fix (see the summary table).

Example

The following example may reproduce the problem:

1. Create an index on a table with included columns:

   CREATE TABLE t (k int PRIMARY KEY, v1 int, v2 int);
   INSERT INTO t SELECT i, i, i * 10 FROM generate_series(1, 10) AS i;
   CREATE INDEX idx_v1_include_v2 ON t (v1 ASC) INCLUDE (v2);
   

2. In a second session, run the following UPDATE while CREATE INDEX in the first session is still in progress.

   UPDATE t SET v2 = v2 + 1000 WHERE k = 1;
   

3. Use the index checker to verify consistency of the index:

   SELECT yb_index_check('idx_v1_include_v2'::regclass);
   

   Example error:

   ERROR:  inconsistent index row due to semantic mismatch of non-key attribute
   DETAIL:  index: 'idx_v1_include_v2', ybbasectid: '\x47121048800000012121', index attnum: 2