Feedback-ID: ia2694551:Fastmail
Date: Tue, 14 Jun 2022 19:48:02 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org>
To: Sergey Aleynikov <sergey.aleynikov@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Xmax precedes relation freeze threshold errors
Message-ID: <20220614174802.cpm66yeoddpr5rgz@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAKNj8S1EtSUSm_sC2a82voU7wSMzhDJcnZXTAfZGgY-mcmAgyQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20220614174802.cpm66yeoddpr5rgz%40alvherre.pgsql>
Precedence: bulk

On 2022-Jun-14, Sergey Aleynikov wrote:

> On Tue, Jun 14, 2022 at 7:12 PM Álvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> > - 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 give some thought to the bits in `t_infomask`.
> 
> I've run verify_heapam() in a loop for a single table. Some runs it
> shows nothing (i've seen three consequent empty runs), and on each run
> it shows something - it reports different pages.

Oh, that definitely sounds like a bug in amcheck then.  Please post the
output of 
 select * from heap_page_items(get_raw_page('the table', the_page));
for some of these pages, preferrably just around the time the errors for
these pages are shown; maybe someone can figure out which tuples cause
these errors and how to fix amcheck.  It might be difficult to catch
tuples 'in the act' though.

Maybe you can script things so that amcheck is run and the output feeds
a script that runs heap_page_items.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Digital and video cameras have this adjustment and film cameras don't for the
same reason dogs and cats lick themselves: because they can."   (Ken Rockwell)