public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: wenhui qiu <qiuwenhuifx@gmail.com>
To: Shinya Kato <shinya11.kato@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
Date: Fri, 31 Oct 2025 16:00:54 +0800
Message-ID: <CAGjGUAKFGC3BW1y1vPmL85r5mr5UWCYK6SmRSPzpHaQxFKF5gQ@mail.gmail.com> (raw)
In-Reply-To: <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
References: <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
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 PM Shinya Kato <shinya11.kato@gmail.com> 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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: qiuwenhuifx@gmail.com, shinya11.kato@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
In-Reply-To: <CAGjGUAKFGC3BW1y1vPmL85r5mr5UWCYK6SmRSPzpHaQxFKF5gQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox