MIME-Version: 1.0
References: <aOaAuXREwnPZVISO@nathan>
 <l7k5nkow2n4x2lodcjimxl4wqv7rdjduo3zuzjwlx3kjxty5q2@gzl4pqbm6ows>
 <aOfcTD3T7F3dydg8@nathan>
 <o33hdbfnosn7pw5e3a34jdtfoaxih6vwbe6rf7bo6ocbn4zv4l@incbnjupgq4o>
In-Reply-To: <o33hdbfnosn7pw5e3a34jdtfoaxih6vwbe6rf7bo6ocbn4zv4l@incbnjupgq4o>
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 9 Oct 2025 15:45:32 -0400
Message-ID: 
 <CAH2-Wz=vmdzeUE7PH9b0igFpJqDKY63icMWmzN=sLiyKVxyqOA@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Andres Freund <andres@anarazel.de>
Cc: Nathan Bossart <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-Wz%3DvmdzeUE7PH9b0igFpJqDKY63icMWmzN%3DsLiyKVxyqOA%40mail.gmail.com>
Precedence: bulk

On Thu, Oct 9, 2025 at 12:15=E2=80=AFPM Andres Freund <andres@anarazel.de> =
wrote:
> > Each worker would consult this table before processing.  If the table i=
s
> > there, it would remove it from the shared table and skip processing it.
> > Then the next worker would try processing the table again.
> >
> > I also wonder how hard it would be to gracefully catch the error and le=
t
> > the worker continue with the rest of its list...
>
> The main set of cases I've seen are when workers get hung up permanently =
in
> corrupt indexes.

How recently was this? I'm aware of problems like that that we
discussed around 2018, but they were greatly mitigated.
First by your commit 3a01f68e, then by my commit c34787f9.

In general, there's no particularly good reason why (at least with
nbtree indexes) VACUUM should ever hang forever. The access pattern is
overwhelmingly simple, sequential access. The only exception is nbtree
page deletion (plus backtracking), where it isn't particularly hard to
just be very careful about self-deadlock.

> There never is actually an error, the autovacuums just get
> terminated as part of whatever independent reason there is to restart.

What do you mean?

In general I'd expect nbtree VACUUM of a corrupt index to either not
fail at all (we'll soldier on to the best of our ability when page
deletion encounters an inconsistency), or to get permanently stuck due
to locking the same page twice/self-deadlock (though as I said, those
problems were mitigated, and might even be almost impossible these
days). Every other case involves some kind of error (e.g., an OOM is
just about possible).

I agree with you about using a perfectly deterministic order coming
with real downsides, without any upside. Don't interpret what I've
said as expressing opposition to that idea.


--
Peter Geoghegan