Date: Wed, 03 Jun 2026 01:05:13 +0000
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
From: Scott Ray <scott@scottray.io>
Cc: shinya11.kato@gmail.com, laurenz.albe@cybertec.at, japinli@hotmail.com,
 qiuwenhuifx@gmail.com, samimseih@gmail.com,
 pgsql-hackers@lists.postgresql.org
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
Message-ID: 
 <qOUvBIi7LC3GLKEjACMQAhPnYaE1m7Ta6XbMXN1gOVo3Mh6-Be6o_kQxQeCNXfo-Ywgc8k6myZrco4a41z-r7Dfqro4-nH73cB_tmSOczJk=@scottray.io>
In-Reply-To: <20260602.165755.1084850050958933598.horikyota.ntt@gmail.com>
References: <6634e1b0b5b74e5c2e3b06201120a81e11d82a55.camel@cybertec.at>
 <3bnBUxwx2npXqvHL0trI11LOOvzQ7LI0GzWqbaj5SJnk7DTb1uzStGveKwj0JJmBW4ebzGIF3az7of4I4rQeaO_PRqDnnClCduPyjM6gPgM=@scottray.io>
 <CAOzEurTC+5xuH_X2EBWjW0Fs9iuTq3OMA=1FfT4xx1wYbCAR6g@mail.gmail.com>
 <20260602.165755.1084850050958933598.horikyota.ntt@gmail.com>
Feedback-ID: 189710061:user:proton
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
 micalg=pgp-sha512;
 boundary="------fe15723b38dc3d4abf41602c6fe22938a836594be6ba67141b62ab352efc8faa";
 charset=utf-8
Archived-At: 
 <https://www.postgresql.org/message-id/qOUvBIi7LC3GLKEjACMQAhPnYaE1m7Ta6XbMXN1gOVo3Mh6-Be6o_kQxQeCNXfo-Ywgc8k6myZrco4a41z-r7Dfqro4-nH73cB_tmSOczJk%3D%40scottray.io>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------fe15723b38dc3d4abf41602c6fe22938a836594be6ba67141b62ab352efc8faa
Content-Type: multipart/mixed;boundary=---------------------8d9f188093e17d92b4db7475537bbb58

-----------------------8d9f188093e17d92b4db7475537bbb58
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;charset=utf-8


> What I would want to investigate after seeing such a signal is not
> necessarily what happened to be blocking that particular VACUUM
> operation at some earlier point, but what is holding the xid horizon
> back now.  For that purpose, a view exposing the current xid/xmin
> holders seems more useful and less misleading than adding a
> best-effort blocker guess to the VACUUM log.

I've been working on a view like this.  It shows the horizon
contribution for each backend, prepared xact, replication slot, and
HSF walsender, broken down by class.  It also shows - for each
contributor - how the horizon would shift if that holder were
removed.

Shinya said [1] that we could have a view in the future.  We could
have both the logging and the view call a single function that reads
the procArray and other sources to gather the horizon information.  I
think the logging and the view would complement each other.

Should I start another thread?

[1] https://www.postgresql.org/message-id/CAOzEurTNVkvvscKeEOy0WwfzyqO+J_M=
yXwkjRteJ_zyydteKCQ@mail.gmail.com

--
Scott Ray
-----------------------8d9f188093e17d92b4db7475537bbb58--

--------fe15723b38dc3d4abf41602c6fe22938a836594be6ba67141b62ab352efc8faa
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: ProtonMail

wrsEARYKAG0FgmoffboJENJWjBT/u5s7RRQAAAAAABwAIHNhbHRAbm90YXRp
b25zLm9wZW5wZ3Bqcy5vcmdONYXppjw8SqOTxURyHaoFEFSTHlcPjN2sEhQP
0KqSohYhBHp15mAbLriqt8a+vNJWjBT/u5s7AAC/IgD+IbYqkYrY8tKtXVVQ
YkFEBqTm7Xcx8slG93AHsqXuoBAA/1tkixsb8DZFI6tvEbUlNRvMqi0Hh+0Z
0qi/tYMfRV4E
=W6GP
-----END PGP SIGNATURE-----


--------fe15723b38dc3d4abf41602c6fe22938a836594be6ba67141b62ab352efc8faa--