MIME-Version: 1.0
References: <6634e1b0b5b74e5c2e3b06201120a81e11d82a55.camel@cybertec.at>
 <3bnBUxwx2npXqvHL0trI11LOOvzQ7LI0GzWqbaj5SJnk7DTb1uzStGveKwj0JJmBW4ebzGIF3az7of4I4rQeaO_PRqDnnClCduPyjM6gPgM=@scottray.io>
 <CAOzEurTC+5xuH_X2EBWjW0Fs9iuTq3OMA=1FfT4xx1wYbCAR6g@mail.gmail.com>
 <20260602.165755.1084850050958933598.horikyota.ntt@gmail.com>
 <CAA5RZ0tF3B-NASWX8kFQ6jbXyRzkpkQh=CuWs6Zmd+Fw3Wu+Fg@mail.gmail.com>
 <CAOzEurTXNzS8C1_pE3ijwhDt-YZgs_H+JCR3dBg9GzKJszdg1Q@mail.gmail.com>
In-Reply-To: 
 <CAOzEurTXNzS8C1_pE3ijwhDt-YZgs_H+JCR3dBg9GzKJszdg1Q@mail.gmail.com>
From: Jacob Champion <jacob.champion@enterprisedb.com>
Date: Wed, 3 Jun 2026 10:36:14 -0700
Message-ID: 
 <CAOYmi+mKfzcj=GbtDhyu49kGwoN5811FqPzFfgvS7R6mzVs4aQ@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: Shinya Kato <shinya11.kato@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>, scott@scottray.io,
	laurenz.albe@cybertec.at, japinli@hotmail.com, qiuwenhuifx@gmail.com,
	pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAOYmi%2BmKfzcj%3DGbtDhyu49kGwoN5811FqPzFfgvS7R6mzVs4aQ%40mail.gmail.com>
Precedence: bulk

(A colleague pointed me at this thread just now, because I somehow
missed it, but I've also been playing around with a solution for this
recently. Your patches are much farther along than mine -- hooray!)

On Tue, Jun 2, 2026 at 6:11=E2=80=AFPM Shinya Kato <shinya11.kato@gmail.com=
> wrote:
> I agree that exposing xid horizon retention information via a
> SQL-visible interface is valuable. However, I believe reporting it
> in the VACUUM log is also important: a view only shows the current
> state, so once a blocker has gone away there is no way to determine,
> after the fact, what was holding the horizon back at the time a
> particular VACUUM ran. Logs are the only durable record we have for
> that kind of post-hoc analysis.

+1. I'd *really* like to have this information logged at the point
that VACUUM is blocked; it'll help during support escalations (or
prevent them!) in a way that a view cannot.

That's not to say we couldn't also have a view, of course.

> That said, I share the concern about emitting best-effort
> information in the VACUUM log, which otherwise reports facts
> observed during the operation. Would it be acceptable if we
> reported the exact blocker -- captured at the moment OldestXmin is
> computed -- rather than a best-effort guess reconstructed
> afterwards?

I don't want to weigh in very strongly here; you've all been thinking
about it for longer than I have. So, taking off the committer hat and
putting my user hat on:

I ran into the same decision between 1) tracking the origin during
horizon calculation and 2) attempting to reconstruct it after the
fact. I decided, for my own patch, that I'd rather track them during
horizon calculation. The cases where I really need these logs are
high-stress, chaotic situations where things are falling apart, and if
the database's answer to "what's causing the problem?" is ever "I
don't know, you better run VACUUM again", I think that's likely to
spike my blood pressure.

As discussed above, though, this approach leads to a collision issue.
So I put the patch down before PGConf.dev, trying to figure out how
best to solve that.

(That said, there's nothing wrong with getting a good solution in and
working on a better one, as long as the first patch doesn't make the
second one harder. Thank you for working on this!)

--Jacob