TA-30104 - Yugabyte Db

Product	Affected Versions	Related Issues	Fixed In
{{<product "ysql">}}	{{<release "2024.2.0">}} to {{<release "2024.2.7.2">}}, {{<release "2025.1.0">}} to {{<release "2025.1.3.0">}}, {{<release "2025.2.0">}}	{{<issue 30104>}}	{{<release "2024.2.7.3">}}, {{<release "2025.1.3.1">}}, {{<release "2025.2.1">}}

Description

INSERT … ON CONFLICT … DO UPDATE queries on a table with a partial or expression index can cause index inconsistencies in the 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 or an expression index.
In case of a partial index, rows that satisfy the partial index predicate are updated by the query so they no longer satisfy the predicate, or vice versa.
In case of an expression index, the query updates a column that is included in the index.

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

Find all partial and expression indexes in the given database:

sql

SELECT
   pi.schemaname,
   pi.tablename,
   pi.indexname,
   pg_get_expr(i.indpred, i.indrelid) AS predicate,
   pg_get_expr(i.indexprs, 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 OR i.indexprs IS NOT NULL) AND
   pi.schemaname NOT IN ('pg_catalog', 'information_schema');

Check each partial or expression index for inconsistency using the yb_index_check() function.
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 that affect partial or expression indexes.
Set the YB-TServer flag ysql_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 and expression indexes, this involves inspection of the columns in the index as well as its predicate or expression(s).

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:

Session A: Create a table with a partial index (expression indexes can be affected similarly), then insert a row that satisfies the index predicate (boolcol = true), so the index contains one entry.
sql
```
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);
```
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 (or expression) index. However, the planner incorrectly skips the index update.
sql
```
INSERT INTO test (k, v, boolcol) VALUES (1, 1, true) ON CONFLICT (k) DO UPDATE SET boolcol = false;
```
Running an index integrity check results in an inconsistency.
sql
```
SELECT yb_index_check('test_v_idx'::regclass);
```