On Thu, Oct 10, 2019 at 1:18 PM Peter Geoghegan <pg@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