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

Hi,

On 2022-05-31 16:43:00 +0200, Alvaro Herrera wrote:
> On 2022-May-26, Christophe Pettus wrote:
>
> > Unless we are 100% sure that amcheck will never return a false
> > negative in this situation, we may have to recommend reindexing
> > everything.
>
> I am not aware of any reason why amcheck would return a false negative.
> Is there any evidence behind this assertion?

I think it's very likely to be possible to have false negatives:

1) It's pretty obvious that you could have constraint violations that went
unnoticed due to the index corruption, were the missing heap-tuple since
has been removed. Consider e.g. a foreign key cascading deletion that
missed the tuple due to the missing index entry - if the
wrongly-not-deleted tuple is subsequently updated, without changing the
fkey relevant columns, everything would look fine from amcheck's view now.

2) It looks like we may not just end up with too few index entries, but can
also end up with multiple index entries for a HOT chain. I don't think
amcheck can detect this right now.

3) I think it's possible to end up with chain bogus root pointers being used
when inserting index entries due to the bug. Those indexes will pass
heapallindexed, but the index entries will point to bogus tuples.

Greetings,

Andres Freund