MIME-Version: 1.0
References: 
 <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
 <CAHGQGwFVdccfhN7PFHU5gZcMNdQuyL8DhZSks2FuuTcZ5V_pGQ@mail.gmail.com>
 <CAGjGUAL-qZ6Na-cH4RY5raF4NwuF47ZVNyP0uMYQcks3w5b7Dw@mail.gmail.com>
 <CAA5RZ0s+UUXekbeGcC-H71kW=MfeaUCOV=yEWX94NXViO2-=pA@mail.gmail.com>
 <CAOzEurQVUsRa45aKdi1Qrc4wVsQKSmbLcN+2A9Eu1W1jm2Y5fw@mail.gmail.com>
 <CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT=9RmiBkAxQkbQ@mail.gmail.com>
 <CAGjGUAJ8JVL9J32ChchmVLgZycEyXf-r-KHc5EiRM3LDiEoPtA@mail.gmail.com>
 <CAOzEurTNVkvvscKeEOy0WwfzyqO+J_MyXwkjRteJ_zyydteKCQ@mail.gmail.com>
In-Reply-To: 
 <CAOzEurTNVkvvscKeEOy0WwfzyqO+J_MyXwkjRteJ_zyydteKCQ@mail.gmail.com>
From: wenhui qiu <qiuwenhuifx@gmail.com>
Date: Mon, 16 Mar 2026 15:59:36 +0800
Message-ID: 
 <CAGjGUAK6j4K3OMocdtPZRwTyF_U7Y47-bzxUDu4ZeUdg35Vtnw@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>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000049daba064d1f9a40"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGjGUAK6j4K3OMocdtPZRwTyF_U7Y47-bzxUDu4ZeUdg35Vtnw%40mail.gmail.com>
Precedence: bulk

--00000000000049daba064d1f9a40
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

HI Shinya
> typedef enum XidHorizonBlockerType
> {
>     XHB_NONE =3D 0,
>     XHB_ACTIVE_TRANSACTION,
>     XHB_IDLE_IN_TRANSACTION,
>     XHB_PREPARED_TRANSACTION,
>     XHB_XMIN_ACTIVE_TRANSACTION,
>     XHB_XMIN_IDLE_IN_TRANSACTION,
>     XHB_HOT_STANDBY_FEEDBACK,
>     XHB_REPLICATION_SLOT,
> }
Thank you for your working on this ,I have another small suggestion
The priority ordering encoded in XidHorizonBlockerType determines which
blocker gets reported when multiple candidates exist. In particular:

ACTIVE_TRANSACTION
IDLE_IN_TRANSACTION
PREPARED_TRANSACTION

Prepared transactions are currently ranked after idle-in-transaction
sessions. Operationally, prepared transactions are often harder for DBAs to
resolve than idle sessions, so it might be worth clarifying the rationale
behind this ordering or reconsidering whether prepared transactions should
have higher priority.
> typedef enum XidHorizonBlockerType
> {
>     XHB_NONE =3D 0,
>     XHB_ACTIVE_TRANSACTION,
>     XHB_PREPARED_TRANSACTION,
>     XHB_IDLE_IN_TRANSACTION,
>     XHB_XMIN_ACTIVE_TRANSACTION,
>     XHB_XMIN_IDLE_IN_TRANSACTION,
>     XHB_HOT_STANDBY_FEEDBACK,
>     XHB_REPLICATION_SLOT,
> }
Another one:
Currently GetXidHorizonBlocker() selects only one blocker (based on the
enum priority) even though multiple independent sources could hold back the
xmin horizon simultaneously. For example, it is possible to have both a
prepared transaction and a replication slot preventing the horizon from
advancing.
Have you considered reporting all detected blockers instead of just the
highest-priority one? Returning only a single entry might hide other
relevant blockers from the user.


Thanks

On Thu, Feb 5, 2026 at 12:40=E2=80=AFPM Shinya Kato <shinya11.kato@gmail.co=
m> wrote:

> HI,
>
> Sorry for the late reply. I've updated the patch to follow Sami's
> recommended approach.
>
> Overview:
> - Instead of modifying ComputeXidHorizons(), this patch introduces two
> new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().
> - GetXidHorizonBlockers() retrieves all potential blockers. This API
> design leaves open the possibility of exposing this information
> through a dynamic statistics view in the future [0].
> - GetXidHorizonBlocker() selects the highest-priority blocker from the
> candidates returned by GetXidHorizonBlockers().
> - Priority is defined in the XidHorizonBlockerType enum. By
> distinguishing whether the blocker matches the horizon via xid or
> xmin, the appropriate blocker is selected.
>
> Changes addressed from review comments:
> - Fixed unstable regression test (Fujii-san's and Andres's comments).
> - When multiple blockers share the same horizon, the blocker with the
> highest priority is now selected for output (Fujii-san's comment).
> - Removed unnecessary code (Fujii-san's comment).
> - Distinguished between active transactions and idle-in-transaction
> sessions, and added tests for both (Sami's and Wenhui's comments).
> - Added a trailing comma to the last value of the enum (Sami's comment).
> - Added a new function GetXidHorizonBlockers(), modeled after
> BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami's
> comment).
> - Added a test for a SERIALIZABLE transaction (Sami's comment).
>
> Not addressed:
> - Did not switch from int to pid_t for the pid type, because int is
> used consistently throughout the PostgreSQL codebase for this purpose
> (Sami's comment).
>
> Other changes:
> - Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.
>
> [0]
> https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP39g9n=
MGZheqtFYfNiO5Bg%40mail.gmail.com
>
>
>
> --
> Best regards,
> Shinya Kato
> NTT OSS Center
>

--00000000000049daba064d1f9a40
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">HI Shinya<div>&gt; typedef enum XidHorizonBlockerType<br>&=
gt; {<br>&gt; =C2=A0 =C2=A0 XHB_NONE =3D 0,<br>&gt; =C2=A0 =C2=A0 XHB_ACTIV=
E_TRANSACTION,<br>&gt; =C2=A0 =C2=A0 XHB_IDLE_IN_TRANSACTION,<br>&gt; =C2=
=A0 =C2=A0 XHB_PREPARED_TRANSACTION,<br>&gt; =C2=A0 =C2=A0 XHB_XMIN_ACTIVE_=
TRANSACTION,<br>&gt; =C2=A0 =C2=A0 XHB_XMIN_IDLE_IN_TRANSACTION,<br>&gt; =
=C2=A0 =C2=A0 XHB_HOT_STANDBY_FEEDBACK,<br>&gt; =C2=A0 =C2=A0 XHB_REPLICATI=
ON_SLOT,<br>&gt; }<br></div><div>Thank you for your working on this ,I have=
 another small suggestion=C2=A0</div><div>The priority ordering encoded in =
XidHorizonBlockerType determines which blocker gets reported when multiple =
candidates exist. In particular:<br></div><div><br>ACTIVE_TRANSACTION<br>ID=
LE_IN_TRANSACTION<br>PREPARED_TRANSACTION<br><br>Prepared transactions are =
currently ranked after idle-in-transaction sessions. Operationally, prepare=
d transactions are often harder for DBAs to resolve than idle sessions, so =
it might be worth clarifying the rationale behind this ordering or reconsid=
ering whether prepared transactions should have higher priority.<br></div><=
div>&gt; typedef enum XidHorizonBlockerType<br>&gt; {<br>&gt; =C2=A0 =C2=A0=
 XHB_NONE =3D 0,<br>&gt; =C2=A0 =C2=A0 XHB_ACTIVE_TRANSACTION,<br>&gt; =C2=
=A0 =C2=A0 XHB_PREPARED_TRANSACTION,<br>&gt; =C2=A0 =C2=A0 XHB_IDLE_IN_TRAN=
SACTION,<br>&gt; =C2=A0 =C2=A0 XHB_XMIN_ACTIVE_TRANSACTION,<br>&gt; =C2=A0 =
=C2=A0 XHB_XMIN_IDLE_IN_TRANSACTION,<br>&gt; =C2=A0 =C2=A0 XHB_HOT_STANDBY_=
FEEDBACK,<br>&gt; =C2=A0 =C2=A0 XHB_REPLICATION_SLOT,<br>&gt; }</div><div>A=
nother one:<br>Currently GetXidHorizonBlocker() selects only one blocker (b=
ased on the enum priority) even though multiple independent sources could h=
old back the xmin horizon simultaneously. For example, it is possible to ha=
ve both a prepared transaction and a replication slot preventing the horizo=
n from advancing.<br>Have you considered reporting all detected blockers in=
stead of just the highest-priority one? Returning only a single entry might=
 hide other relevant blockers from the user.<br></div><div><br></div><div><=
br></div><div>Thanks</div></div><br><div class=3D"gmail_quote gmail_quote_c=
ontainer"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Feb 5, 2026 at 12:4=
0=E2=80=AFPM Shinya Kato &lt;<a href=3D"mailto:shinya11.kato@gmail.com">shi=
nya11.kato@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-sty=
le:solid;border-left-color:rgb(204,204,204);padding-left:1ex">HI,<br>
<br>
Sorry for the late reply. I&#39;ve updated the patch to follow Sami&#39;s<b=
r>
recommended approach.<br>
<br>
Overview:<br>
- Instead of modifying ComputeXidHorizons(), this patch introduces two<br>
new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().<br>
- GetXidHorizonBlockers() retrieves all potential blockers. This API<br>
design leaves open the possibility of exposing this information<br>
through a dynamic statistics view in the future [0].<br>
- GetXidHorizonBlocker() selects the highest-priority blocker from the<br>
candidates returned by GetXidHorizonBlockers().<br>
- Priority is defined in the XidHorizonBlockerType enum. By<br>
distinguishing whether the blocker matches the horizon via xid or<br>
xmin, the appropriate blocker is selected.<br>
<br>
Changes addressed from review comments:<br>
- Fixed unstable regression test (Fujii-san&#39;s and Andres&#39;s comments=
).<br>
- When multiple blockers share the same horizon, the blocker with the<br>
highest priority is now selected for output (Fujii-san&#39;s comment).<br>
- Removed unnecessary code (Fujii-san&#39;s comment).<br>
- Distinguished between active transactions and idle-in-transaction<br>
sessions, and added tests for both (Sami&#39;s and Wenhui&#39;s comments).<=
br>
- Added a trailing comma to the last value of the enum (Sami&#39;s comment)=
.<br>
- Added a new function GetXidHorizonBlockers(), modeled after<br>
BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami&#39;s<b=
r>
comment).<br>
- Added a test for a SERIALIZABLE transaction (Sami&#39;s comment).<br>
<br>
Not addressed:<br>
- Did not switch from int to pid_t for the pid type, because int is<br>
used consistently throughout the PostgreSQL codebase for this purpose<br>
(Sami&#39;s comment).<br>
<br>
Other changes:<br>
- Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.<br>
<br>
[0] <a href=3D"https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQU=
MK3VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gmail.com" rel=3D"noreferrer" target=
=3D"_blank">https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3=
VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gmail.com</a><br>
<br>
<br>
<br>
--<br>
Best regards,<br>
Shinya Kato<br>
NTT OSS Center<br>
</blockquote></div>

--00000000000049daba064d1f9a40--