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