Date: Wed, 10 Jan 2024 11:38:51 -0800
From: Noah Misch <noah@leadboat.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>,
	robertmhaas@gmail.com, Alexander Lakhin <exclusion@gmail.com>,
	PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
	Andres Freund <andres@anarazel.de>
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
Message-ID: <20240110193851.f0.nmisch@google.com>
References: <17257-1e46de26bec11433@postgresql.org>
 <d5d5af5d-ba46-aff3-9f91-776c70246cc3@gmail.com>
 <CAH2-WzknORjSVufsDQKr7VVnXspRhdsjKBh8brwHEkrUpqVXjg@mail.gmail.com>
 <20231225024402.77@rfd.leadboat.com>
 <CAH2-Wz=X88igoxQvAGW2cFrTsEXK+VJJHap=90-z=juD5DqYdw@mail.gmail.com>
 <20240106202413.e5@rfd.leadboat.com>
 <CAH2-Wzk+H1PLdYm8K8M8Uaz_ikXNob8a2KBMd_JRALrzwPcjww@mail.gmail.com>
 <20240109214447.88@rfd.leadboat.com>
 <CAH2-Wzn57T=d7eB90m0wr+AiAXetk-NWA=ntS89R2mOcDimNsQ@mail.gmail.com>
 <CAH2-Wznv94Q_Td8OS8bAN7fYLpfU6CGgjn6Xau5eJ_sDxEGeBA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAH2-Wznv94Q_Td8OS8bAN7fYLpfU6CGgjn6Xau5eJ_sDxEGeBA@mail.gmail.com>
User-Agent: Mutt/2.2.12 (2023-09-09)
Archived-At: 
 <https://www.postgresql.org/message-id/20240110193851.f0.nmisch%40google.com>
Precedence: bulk

On Wed, Jan 10, 2024 at 02:06:42PM -0500, Peter Geoghegan wrote:
> On Tue, Jan 9, 2024 at 6:16 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > On Tue, Jan 9, 2024 at 4:44 PM Noah Misch <noah@leadboat.com> wrote:
> > > On Tue, Jan 09, 2024 at 03:59:19PM -0500, Peter Geoghegan wrote:
> > > > Did the affected system that you investigated happen to have an
> > > > atypically high number of databases? The system 15.4 system that I saw
> > > > the problem on had almost 3,000 databases.
> > >
> > > No, single-digit database count here.
> >
> > My suspicion was that this factor might increase the propensity of
> > calls to GetOldestNonRemovableTransactionId (used to establish
> > VACUUM's OldestXmin) to not agree with the GlobalVis* based state used
> > by pruneheap.c, in the way that we need to worry about here (i.e.
> > inconsistencies that lead to VACUUM getting stuck inside
> > lazy_scan_prune's loop).
> 
> Another question about your database/system: does VACUUM get stuck
> while scanning a page some time after it has already completed a round
> of index vacuuming?

I don't know.  That particular system experienced the infinite loop only once.

> That's what I see here -- I don't think that pruning leaves behind
> even a single live heap tuple, despite scanning thousands of pages
> before reaching the page that it gets stuck on. Could be another red
> herring. But it doesn't seem impossible that some of the nbtree calls
> to procarray.c routines performed by code added by my commit
> 9dd963ae25, "Recycle nbtree pages deleted during same VACUUM", are
> somehow related. That is, that code could be part of the chain of
> events that cause the problem (whether or not the code itself is
> technically at fault).
> 
> I'm referring to calls such as the
> "GetOldestNonRemovableTransactionId(NULL)" and
> "GlobalVisCheckRemovableFullXid()" calls that take place inside
> _bt_pendingfsm_finalize(). It's not like we do stuff like that in very
> many other places.

I see what you mean about the rarity and potential importance of
"GetOldestNonRemovableTransactionId(NULL)".  There's just one other caller,
vac_update_datfrozenxid(), which calls it for an unrelated cause.