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

On Tue, May 31, 2022 at 7:43 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> I am not aware of any reason why amcheck would return a false negative.
> Is there any evidence behind this assertion?

With heapallindexed verification, there is a theoretical risk of them
from the Bloom filter itself, due to hash collisions. But the chance
of that happening is rather low. And there is a random seed, so absent
an extreme shortage of memory the collision shouldn't recur on a
second or a third attempt at verification. Or with a second or third
missing tuple in any single attempt at verifying a corrupt index.

> If there is none, the recommendation should be to use amcheck on all
> btree indexes and reindex those that have the problem; and reindex all
> indexes of other AMs that could have been reindexed or created
> concurrently in 14beta1 or later (implying: an index that was created in
> 13 and pg_upgraded but not touched afterwards is not at risk).

I'm in favor of this.

We agonized about these false negatives back in 2017, when the feature
went in, but I think that they're a negligible issue compared to
everything else. It just doesn't need to be discussed in the release
notes. The large majority of people that do this will find nothing at
all, because there is no corruption.

Lots of people that already use amcheck probably don't use
heapallindexed verification at all. For one thing it's not the default
with pg_amcheck. It's also significantly more expensive.

-- 
Peter Geoghegan