Pg14 possible index corruption after reindex concurrently - Mailing list pgsql-general

Hello,

we have a problem with an index on a database we recently upgraded from PG13 to Pg14.3 using pg_upgrade. After all the upgrade steps including analyze in stages, we run  "vacuumdb -Fvaz -j 8" and the user workload was started afterward.

In order to get one of the Pg14 benefits (b-tree deduplication), we decided to rebuild all indexes:

-- CONCURRENTLY can not be used for REINDEX DATABASE and system catalog was brand new on the upgraded database, so no need to reindex system.

SELECT format('REINDEX SCHEMA CONCURRENTLY %I;', n.nspname)
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;

\gexec

PG Version: PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

OS: CentOS 7

Indexed columns are BIGINT (so no collation issues).

A few days later we realized, that some queries did not return the expected data. By disabling indexscan and bitmapindex scan, expected row was found using sequential scan.

Now we have a table (80GB, 314.108.951 rows) with a reproducible testcase to demonstrate that using the primary key, requested data are returned while using another index no rows are returned.

Testcase:

START TRANSACTION;

EXPLAIN ANALYZE SELECT * FROM opportunities.tab_odds WHERE id_odds = 1652734429;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pk_tabodds_idodds on tab_odds  (cost=0.57..2.59 rows=1 width=229) (actual time=0.076..0.078 rows=1 loops=1)
   Index Cond: (id_odds = 1652734429)
 Planning Time: 0.152 ms
 Execution Time: 0.119 ms
(4 rows)

ALTER TABLE opportunities.tab_odds DROP CONSTRAINT pk_tabodds_idodds CASCADE;

EXPLAIN ANALYZE SELECT * FROM opportunities.tab_odds WHERE id_odds = 1652734429;
                                                                 QUERY PLAN                                                                
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ix_tabodds_idodds_idopportunity on tab_odds  (cost=0.57..2.59 rows=1 width=229) (actual time=0.026..0.027 rows=0 loops=1)
   Index Cond: (id_odds = 1652734429)
 Planning Time: 0.248 ms
 Execution Time: 0.072 ms
(4 rows)

ROLLBACK;

The indexes are:

Indexes:
    "pk_tabodds_idodds" PRIMARY KEY, btree (id_odds)
    "ix_tabodds_idodds_idopportunity" btree (id_odds, id_opportunity)

While we can do another reindex and check query results, we would like to be able to identify such issues systematically rather than by en user complaints.

I've tried to use amcheck extension to validate the index:

select * from bt_index_check('opportunities.ix_tabodds_idodds_idopportunity'::regclass);

 bt_index_check
----------------
 
(1 row)

Running the pg_amcheck utility on the table (including its indexes):

/usr/pgsql-14/bin/pg_amcheck -d prematch -r opportunities.tab_odds -j 8 -P ; echo $?

heap table "prematch.opportunities.tab_odds", block 1579813, offset 62:
    xmax 4051057343 precedes relation freeze threshold 2:3960858664
heap table "prematch.opportunities.tab_odds", block 1580759, offset 43:
    xmin 4051053353 precedes relation freeze threshold 2:3960858664

....

heap table "prematch.opportunities.tab_odds", block 2164163, offset 5:
    xmin 4051075211 precedes relation freeze threshold 2:3960858664
heap table "prematch.opportunities.tab_odds", block 2164163, offset 6:
    xmin 4051075236 precedes relation freeze threshold 2:3960858664
heap table "prematch.opportunities.tab_odds", block 2164163, offset 7:
    xmin 4051075236 precedes relation freeze threshold 2:3960858664
heap table "prematch.opportunities.tab_odds", block 2164164, offset 1:
    xmin 4051075260 precedes relation freeze threshold 2:3960858664

2

The default access method check returns exit code2, while when using --rootdescend option  return code is zero and no xmin/xmax messages:

/usr/pgsql-14/bin/pg_amcheck -d prematch -r opportunities.tab_odds --rootdescend -j 8 -P ; echo $?                                                                            
 0/15 relations (0%),        0/16831435 pages (0%)
 9/15 relations (60%), 16831427/16831435 pages (99%)
10/15 relations (66%), 16831429/16831435 pages (99%)
11/15 relations (73%), 16831431/16831435 pages (99%)
12/15 relations (80%), 16831433/16831435 pages (99%)
13/15 relations (86%), 16831434/16831435 pages (99%)
14/15 relations (93%), 16831435/16831435 pages (100%)
15/15 relations (100%), 16831435/16831435 pages (100%)
0

I've thought about using pageinspect to dump the invalid index page, but haven't found a way how to identify the index block number.

Is there a way, how to diagnose such corruption to make sure that after (and ideally also before) upgrading the database no such corruption happened (I'd like to test the diagnostic approach before rebuilding the index)?

Unfortunately, the cluster was so old, and planned outage was limited - page checksums were not enabled yet.

Thanks for any recommendations for diagnostic and or mitigation.

Ales