MIME-Version: 1.0
References: 
 <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
In-Reply-To: 
 <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
From: wenhui qiu <qiuwenhuifx@gmail.com>
Date: Fri, 31 Oct 2025 16:00:54 +0800
Message-ID: 
 <CAGjGUAKFGC3BW1y1vPmL85r5mr5UWCYK6SmRSPzpHaQxFKF5gQ@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: Shinya Kato <shinya11.kato@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000090496806426fc496"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGjGUAKFGC3BW1y1vPmL85r5mr5UWCYK6SmRSPzpHaQxFKF5gQ%40mail.gmail.com>
Precedence: bulk

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

HI
  Thank you for your path ,This path is extremely helpful.
> +/*
> + * Identifies what determined a relation's OldestXmin horizon.
> + * Used by autovacuum to report why dead tuples were not removable.
> + */
> +typedef enum OldestXminSource
> +{
> + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> + OLDESTXMIN_SOURCE_OTHER
> +} OldestXminSource;
> +
> +typedef struct OldestXminInfo
> +{
> + OldestXminSource source;
> + int backend_pid;
> +} OldestXminInfo;
I have a question for like this
one session
begin;
select * from table_a
not commit or not closed session
It is in OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION type ?


Thank

On Fri, Oct 31, 2025 at 2:32=E2=80=AFPM Shinya Kato <shinya11.kato@gmail.co=
m> wrote:

> Hi hackers,
>
> I am proposing to add the reason for the oldest xmin to VACUUM logs.
> This feature would be useful for identifying why dead tuples cannot be
> removed, thereby helping to diagnose and prevent table bloat.
>
> The current logs only indicate that dead tuples could not be reclaimed
> due to the oldest xmin, but they do not reveal the underlying reason.
> To identify the cause, it is necessary to query multiple views:
> pg_stat_activity (for active transactions), pg_prepared_xacts (for
> prepared statements), pg_replication_slots (for replication slots),
> and pg_stat_replication (for hot standby feedback). However, because
> the data in these views is volatile, it is difficult to retroactively
> determine what was holding the oldest xmin at the specific time the
> log message was generated.
>
> This PoC patch addresses this problem. The implementation now outputs
> the reason for the oldest xmin and, where applicable, the backend PID.
> This information was originally discarded when calculating the oldest
> xmin horizon, and the computation required to retrieve these reasons
> is considered reasonable.
>
> The patch is attached. What do you think?
>
> --
> Best regards,
> Shinya Kato
> NTT OSS Center
>

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

<div dir=3D"ltr">HI=C2=A0<div>=C2=A0 Thank you for your path ,This path is =
extremely helpful.</div><div>&gt; +/*<br>&gt; + * Identifies what determine=
d a relation&#39;s OldestXmin horizon.<br>&gt;<span class=3D"gmail-Apple-co=
nverted-space">=C2=A0</span>+ * Used by autovacuum to report why dead tuple=
s were not removable.<br>&gt;<span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>+ */<br>&gt;<span class=3D"gmail-Apple-converted-space">=C2=A0=
</span>+typedef enum OldestXminSource<br>&gt;<span class=3D"gmail-Apple-con=
verted-space">=C2=A0</span>+{<br>&gt;<span class=3D"gmail-Apple-converted-s=
pace">=C2=A0</span>+	OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,<br>&gt;<span cla=
ss=3D"gmail-Apple-converted-space">=C2=A0</span>+	OLDESTXMIN_SOURCE_HOT_STA=
NDBY_FEEDBACK,<br>&gt;<span class=3D"gmail-Apple-converted-space">=C2=A0</s=
pan>+	OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,<br>&gt;<span class=3D"gmail-A=
pple-converted-space">=C2=A0</span>+	OLDESTXMIN_SOURCE_REPLICATION_SLOT,<br=
>&gt;<span class=3D"gmail-Apple-converted-space">=C2=A0</span>+	OLDESTXMIN_=
SOURCE_OTHER<br>&gt;<span class=3D"gmail-Apple-converted-space">=C2=A0</spa=
n>+} OldestXminSource;<br>&gt;<span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>+<br>&gt;<span class=3D"gmail-Apple-converted-space">=C2=A0</s=
pan>+typedef struct OldestXminInfo<br>&gt;<span class=3D"gmail-Apple-conver=
ted-space">=C2=A0</span>+{<br>&gt;<span class=3D"gmail-Apple-converted-spac=
e">=C2=A0</span>+	OldestXminSource source;<br>&gt;<span class=3D"gmail-Appl=
e-converted-space">=C2=A0</span>+	int			backend_pid;<br>&gt;<span class=3D"=
gmail-Apple-converted-space">=C2=A0</span>+} OldestXminInfo;<br></div><div>=
I have a question for like this=C2=A0</div><div>one session=C2=A0</div><div=
>begin;</div><div>select * from table_a</div><div>not commit or not closed =
session=C2=A0</div><div>It is in OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION type =
?</div><div><br></div><div><br></div><div><br></div><div>Thank=C2=A0</div><=
/div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" =
class=3D"gmail_attr">On Fri, Oct 31, 2025 at 2:32=E2=80=AFPM Shinya Kato &l=
t;<a href=3D"mailto:shinya11.kato@gmail.com">shinya11.kato@gmail.com</a>&gt=
; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:=
rgb(204,204,204);padding-left:1ex">Hi hackers,<br>
<br>
I am proposing to add the reason for the oldest xmin to VACUUM logs.<br>
This feature would be useful for identifying why dead tuples cannot be<br>
removed, thereby helping to diagnose and prevent table bloat.<br>
<br>
The current logs only indicate that dead tuples could not be reclaimed<br>
due to the oldest xmin, but they do not reveal the underlying reason.<br>
To identify the cause, it is necessary to query multiple views:<br>
pg_stat_activity (for active transactions), pg_prepared_xacts (for<br>
prepared statements), pg_replication_slots (for replication slots),<br>
and pg_stat_replication (for hot standby feedback). However, because<br>
the data in these views is volatile, it is difficult to retroactively<br>
determine what was holding the oldest xmin at the specific time the<br>
log message was generated.<br>
<br>
This PoC patch addresses this problem. The implementation now outputs<br>
the reason for the oldest xmin and, where applicable, the backend PID.<br>
This information was originally discarded when calculating the oldest<br>
xmin horizon, and the computation required to retrieve these reasons<br>
is considered reasonable.<br>
<br>
The patch is attached. What do you think?<br>
<br>
-- <br>
Best regards,<br>
Shinya Kato<br>
NTT OSS Center<br>
</blockquote></div>

--00000000000090496806426fc496--