BUG #17485: Records missing from Primary Key index when doing REINDEX INDEX CONCURRENTLY

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: pet(dot)slavov(at)gmail(dot)com
Subject: BUG #17485: Records missing from Primary Key index when doing REINDEX INDEX CONCURRENTLY
Date: 2022-05-18 10:42:13
Message-ID: 17485-396609c6925b982d@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17485
Logged by: Peter Slavov
Email address: pet(dot)slavov(at)gmail(dot)com
PostgreSQL version: 14.3
Operating system: Ubuntu 20.04
Description:

I a noticed a problem in our production database when rebuilding the primary
key index. The database has a lot of INSERTS and UPDATES when this is
happening. I was able to reproduce this on PostgreSQL 14.1/2/3 locally on
docker instance and on AWS EC2.
Here is how you can reproduce:
- Load table `test2` from this file containing the table structure and small
testing dataset:

https://drive.google.com/file/d/1vCqKOda3IIrHmWDzNNJaDQfCb5eQA_D2/view?usp=sharing
- Start inserting and updating in this table using pgbench:
SHELL: pgbench -r -T 1000 -f test.sql -c 50 -j 50 receiptbank
test.sql file:
https://drive.google.com/file/d/1mdduGqu1XcDg01iwSysF7szrIlCKUJQT/view?usp=sharing
- Reindex the PRIMARY KEY (it is possible to have to repeat this 2-3 times):

SQL: REINDEX INDEX CONCURRENTLY test2_pkey
- Check index with `amcheck` extension for errors:
SQL: select bt_index_check(index => c.oid, heapallindexed => true) from
pg_class c where oid = 'test2_pkey'::regclass;
SQL result for me:
ERROR: heap tuple (13134,18) from table "test2" lacks matching index
tuple within index "test2_pkey"
- Check for missing records in the index:
SQL: WITH missing_check AS (
SELECT t1.*, t1.ctid, EXISTS(SELECT 1 FROM test2 t2 WHERE
t2.id = t1.id) AS flag
FROM test2 t1)
SELECT id, ctid
FROM missing_check
WHERE flag = false;
Result for me was several records found ONLY with the sequential
scan...

This problem is possibly related to BUG #17478

Thanks for the help,
Peter Slavov

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2022-05-18 10:54:37 Re: BUG #17484: Query does not prune partitions correctly
Previous Message PG Bug reporting form 2022-05-18 09:37:20 BUG #17484: Query does not prune partitions correctly