Description

INSERT … ON CONFLICT … DO UPDATE queries on a table with a partial index can cause index inconsistencies in the partial index.

The following conditions need to be true for the index to become inconsistent:

• The query uses INSERT ON CONFLICT DO UPDATE.
• The query must be the first one in the session (connection) to reference the table.
• The table contains a partial index.
• Rows that satisfy the partial index predicate are updated by the query to no longer satisfy the predicate, or vice versa.

The YugabyteDB query planner skips index updates when an index row remains unmodified by the update. The planner fails to read the index's predicates, and when no other columns in the index are updated, the planner assumes that updating the index is a no-op (no operation). This results in incorrectly skipping the index update.

Mitigation

Identify and fix inconsistent indexes

1. Find all partial indexes in a given database:

   SELECT
      pi.schemaname,
      pi.tablename,
      pi.indexname,
      pg_get_expr(i.indpred, i.indrelid) AS expression,
      pi.indexdef  -- Shows the full CREATE INDEX statement
   FROM
      pg_indexes AS pi
   JOIN
      pg_index AS i ON i.indexrelid = (pi.schemaname || '.' || pi.indexname)::regclass
   WHERE
      i.indpred IS NOT NULL
      AND pi.schemaname NOT IN ('pg_catalog', 'information_schema');
   

2. Check each partial index for inconsistency using the yb_index_check() function.

3. If yb_index_check() reports any index as inconsistent, then drop and recreate the affected index.

Avoid inconsistent indexes

Any of the following workarounds can be used to avoid inconsistent indexes in the future:

• Upgrade to a YugabyteDB version containing the fix.
• If the YugabyteDB version does not include a fix for this issue, then avoid INSERT … ON CONFLICT … DO UPDATE queries involving the partial index.
• Set the yb-tserver flag yb_skip_redundant_update_ops to false.

Details

The query planner has an optimization to skip updating an index on INSERT ... ON CONFLICT ... DO UPDATE when it determines that the contents of the index row remain unmodified by the UPDATE. For partial indexes, this involves inspection of the columns in the index as well as its index predicate.

PostgreSQL has a planning-time optimization that skips the multi-step planning process for trivial INSERT queries. A side effect of this optimization is that the relation's catalog information (its attributes, indexes, constraints) is not loaded into the planner's data structures. Due to this optimization, the planner incorrectly concludes that the index is unmodified and skips updating it.

Example

The following sequence demonstrates the bug:

1. Session A: Create a table with a partial index, then insert a row that satisfies the index predicate (boolcol = true), so the index contains one entry.

   CREATE TABLE test (k INT PRIMARY KEY, v INT, boolcol BOOL);
   CREATE UNIQUE INDEX test_v_idx ON test (v) WHERE boolcol = true;
   
   INSERT INTO test (k, v, boolcol) VALUES (1, 1, true);
   

2. Session B: Execute an INSERT ... ON CONFLICT statement as the first interaction with the table in a new session. This update changes boolcol to false, meaning the row should be removed from the partial index. However, the planner incorrectly skips the index update.

   INSERT INTO test (k, v, boolcol) VALUES (1, 1, true) ON CONFLICT (k) DO UPDATE SET boolcol = false;    
   

3. Running an index integrity check results in an inconsistency.

   SELECT yb_index_check('test_v_idx'::regclass);