From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Intermittent buildfarm failures on wrasse
Date: Wed, 13 Apr 2022 19:38:06 -0400
Message-ID: <1364997.1649893086@sss.pgh.pa.us> (raw)
In-Reply-To: <CAH2-WzmOJ5YeT1SsBZ7kp2B+EMMVH9RYShornT13oyduEVwKSw@mail.gmail.com>
References: <1346227.1649887693@sss.pgh.pa.us>
<CAH2-WzkVEJWgJ6FrPd0jNiFo3e4kADNYSDSnQXWQbktqWPZfkA@mail.gmail.com>
<1349964.1649890446@sss.pgh.pa.us>
<CAApHDvrHtXtDCjbdRLJAKOO6warCd4WELxK9tq0mUz3u1OaLOg@mail.gmail.com>
<9F59C4BB-3C82-44B0-9B10-4A2CCC3DE552@anarazel.de>
<CAH2-WzmOJ5YeT1SsBZ7kp2B+EMMVH9RYShornT13oyduEVwKSw@mail.gmail.com>
Peter Geoghegan <pg@bowt.ie> writes:
> On Wed, Apr 13, 2022 at 4:13 PM Andres Freund <andres@anarazel.de> wrote:
>> IIRC the problem in matter isn't skipped pages, but that the horizon simply isn't new enough to mark pages as all visible.
> Sometimes OldestXmin can go backwards in VACUUM operations that are
> run in close succession against the same table, due to activity from
> other databases in the same cluster (perhaps other factors are
> involved at times).
I've been doing some testing locally by inserting commands to
manually set tenk1's relallvisible to zero. I first did that
in test_setup.sql ... and it had no effect whatsoever. Further
experimentation showed that the "CREATE INDEX ON tenk1" steps
in create_index.sql itself generally suffice to fix relallvisible;
although if you force it back to zero after the last such command,
you get the same plan diffs wrasse is showing. And you don't
get any others, which I thought curious until I realized that
sanity_check.sql's database-wide VACUUM offers yet another
opportunity to heal the incorrect value. If you force it back
to zero again after that, a bunch of later tests start to show
plan differences, which is what I'd been expecting.
So what seems to be happening on wrasse is that a background
autovacuum (or really autoanalyze?) is preventing pages from
being marked all-visible not only during test_setup.sql but
also create_index.sql; but it's gone by the time sanity_check.sql
runs. Which is odd in itself because not that much time elapses
between create_index and sanity_check, certainly less than the
time from test_setup to create_index.
It seems like a reliable fix might require test_setup to wait
for any background autovac to exit before it does its own
vacuums. Ick.
And we still lack an explanation of why this only now broke.
I remain suspicious that pgstats is behaving unexpectedly.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pg@bowt.ie, andres@anarazel.de, dgrowleyml@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Intermittent buildfarm failures on wrasse
In-Reply-To: <1364997.1649893086@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox