Feedback-ID: ia2694551:Fastmail
User-Agent: Cyrus-JMAP/3.7.0-alpha0-712-gb9e94258b0-fm-20220610.001-gb9e94258
Mime-Version: 1.0
Message-Id: <ce53f0a4-ff33-40a7-b8b1-80b524aee52d@www.fastmail.com>
In-Reply-To: 
 <CAKNj8S31JjOmRjP77QNCY9Zr8_098V_UJJHn9zMoyMQShHfoMQ@mail.gmail.com>
References: 
 <CAKNj8S31JjOmRjP77QNCY9Zr8_098V_UJJHn9zMoyMQShHfoMQ@mail.gmail.com>
Date: Tue, 14 Jun 2022 18:12:08 +0200
From: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>
To: "Sergey Aleynikov" <sergey.aleynikov@gmail.com>,
 pgsql-admin@lists.postgresql.org
Subject: Re: Xmax precedes relation freeze threshold errors
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/ce53f0a4-ff33-40a7-b8b1-80b524aee52d%40www.fastmail.com>
Precedence: bulk


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 =E2=80=9Cpoker.public.user_tournament=E2=80=9D, 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 just that time, or a NFS failure).  This would cause =
these tuples, which should have disappeared, to remain.  This is hard to=
 solve: you have to figure out which tuples were those that should have =
been removed, and delete them, while at the same time retaining any tupl=
es that were added to those pages afterwards.  This is not super common,=
 but it's definitely a 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't think 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 giv=
e 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 to follow 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 e=
nd of the table, then the first possibility sounds the most likely of th=
ese three. However, if you have dozens of tables with the same problem, =
there might be something else going on.