Re: BUG #17245: Index corruption involving deduplicated entries

On Sun, Oct 24, 2021 at 2:35 PM K. R. <iijima.yun@koumakan.jp> wrote:
> $ ./pg_amcheck -U postgres --heapallindexed --parent-check --rootdescend
> azurlane_wiki
> btree index "azurlane_wiki.mediawiki.page_redirect_namespace_len":
>      ERROR:  posting list contains misplaced TID in index
> "page_redirect_namespace_len"
>      DETAIL:  Index tid=(14,9) posting list offset=110 page lsn=2/2C4F7CD8.
> btree index "azurlane_wiki.mediawiki.page_len":
>      ERROR:  posting list contains misplaced TID in index "page_len"
>      DETAIL:  Index tid=(1,2) posting list offset=34 page lsn=2/2DDA2378.
> btree index "azurlane_wiki.mediawiki.transcode_key_idx":
>      ERROR:  posting list contains misplaced TID in index
> "transcode_key_idx"
>      DETAIL:  Index tid=(1,9) posting list offset=5 page lsn=2/2B53AED8.

Thanks for getting back to me with that so quickly.

I suspect that these posting list tuples have duplicate TIDs, which is
never supposed to happen -- nbtree expects that heapam (and the
broader system) will never allow a duplicate TID to be present in the
whole index.

It's possible that I'm wrong, and the corrupt posting list TIDs are
actually in the wrong order (i.e. they're all unique, but are somehow
not in perfect TID-wise order). But I doubt it.

> After REINDEX'ing the three indices listed by pg_amcheck, there is no
> apparent change to the state of the page_main_title index:

> azurlane_wiki=# select ctid, page_title from mediawiki.page WHERE
> page_title = 'Kaga' and page_namespace = 0;
>     ctid   | page_title
> ----------+------------
>   (446,32) | Belfast
>   (720,53) | Kaga
> (2 rows)

I'm a little confused.

Do you mean that even a REINDEX isn't enough to stop the index from
giving this particular wrong answer, where it thinks that 'Belfast' is
the same as 'Kaga'? Even a REINDEX won't make it stop doing that? Or,
are you concerned that pg_amcheck doesn't detect a remaining problem
with one index?

Thanks
-- 
Peter Geoghegan