Re: BUG #17245: Index corruption involving deduplicated entries

On Sun, Oct 24, 2021 at 5:56 PM Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
> Nope. The database in question was restored from a pg_dumpall backup
> three weeks ago and I'm told there have been no crashes or even unclean
> restarts since then.

Anything is possible, but this doesn't look like logical index
corruption. If we assume that it was something like a faulty
deduplication pass, then we have to explain how the spurious TID ended
up in that particular leaf page to begin with. We also have to explain
why it is a valid TID though totally distinct TID, which is unlikely
to occur by chance.

I find it slightly suspicious that the "correct" heap blocks (those
blocks that the correct heap tuple is located) are close together in
the heap -- 720 (in the case of 'Kaga'), 722 (in the case of
'Isokaze'). While the "incorrect" heap blocks (those blocks that the
non-matching heap tuple is located, that are nevertheless returned by
the index scan) are earlier in the table -- 181 and 446.

> That also doesn't seem likely from the heap page images I saw, which
> suggested a fairly low update rate, HOT updates, and page cleanup rather
> than much vacuum activity (in particular there were several LP_REDIRECT
> pointers and few free pointers).

FWIW Postgres 14 can truncate a heap page's line pointer array during
VACUUM's second pass -- see commit 3c3b8a4b. So a lack of observable
free line pointers is not quite the signal it once was.

What does pg_stat_user_tables say about autovacuum?

--
Peter Geoghegan