Re: Xmax precedes relation freeze threshold errors

On Tue, Jun 14, 2022, at 2:27 PM, Sergey Aleynikov wrote:
> /usr/lib/postgresql/14/bin/pg_amcheck -h /var/run/pgsql15/ -d poker -t
> public.user_tournament --heapallindexed
> heap table “poker.public.user_tournament”, block 30356, offset 125:
>     xmax 634989520 precedes relation freeze threshold 12:634871433

Hmm, some wild guesses:

- There used to be a bug whereby VACUUM failed to truncate pages at the end of a relation when all their tuples were
removed;in order for this to happen, you need some disaster to occur at the end of vacuum (such as a system crash at
justthat time, or a NFS failure).  This would cause these tuples, which should have disappeared, to remain.  This is
hardto solve: you have to figure out which tuples were those that should have been removed, and delete them, while at
thesame time retaining any tuples that were added to those pages afterwards.  This is not super common, but it's
definitelya possibility. 

- Maybe there's some bug in amcheck that causes it to report tuple with an old xmax but which in reality are frozen?  I
don'tthink this is very likely, but In order to discard this hypothesis, you'd have to show the output of
`heap_page_items`from the pages in question, or at least give some thought to the bits in `t_infomask`. 

- Maybe you promoted a standby in some wrong way. I don't know what this entails, but I've seen it claimed that failing
tofollow the documented procedures exactly, you might end up with broken data pages. 

If these block numbers are, or were at some time in the past, near the end of the table, then the first possibility
soundsthe most likely of these three. However, if you have dozens of tables with the same problem, there might be
somethingelse going on.