On Thu, Oct 10, 2019 at 1:18 PM Peter Geoghegan <p...@bowt.ie> wrote: > You didn't say which PostgreSQL versions were involved, and if the > database was ever upgraded using pg_upgrade. Those details could > matter.
In case you weren't aware, contrib/amcheck should make detected and diagnosing these kinds of problems a lot easier. You should prefer to use the bt_index_parent_check() variant (which will block writes and VACUUM, but not reads). It will verify that sibling pointers are in agreement with each other, and that leaf pages contain keys that are covered by the relevant separator keys from the parent level. If you happen to be using v11, then you might also want to use the heapallindexed option -- that will verify that the heap and index are in agreement. If the issue is on v12, the new "rootdescend" option can detect very subtle cross-level transitive consistency options. (This is only available in v12 because that was the version that made all entries in the index unique by using heap TID as a unique-ifier.) -- Peter Geoghegan