public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: wenhui qiu <qiuwenhuifx@gmail.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: 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: Mon, 17 Nov 2025 09:43:05 +0800
Message-ID: <CAGjGUAK7mrwoQQYH=GtsfjTWRASACj1MY99iWQYZcacpMRxp5w@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0s+UUXekbeGcC-H71kW=MfeaUCOV=yEWX94NXViO2-=pA@mail.gmail.com>
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>
Hi Sami
> Thinking about point 3 above, I began to wonder if this
> whole thing can be simplified with inspiration. Looking at the
> existing BackendXidGetPid(), I think it can.
> Based on BackendXidGetPid(), I tried a new routine called
> BackendXidFindCutOffReason() which can take in the cutoff xmin,
> passed in by vacuum and can walk though the proc array and
> determine the reason. We don't need to touch ComputeXidHorizons()
> to make this work, it seems to me. This comes with an additional
> walk though the procarray holding a shared lock, but I don't think
> this will be an issue.
> Attached is a rough sketch of BackendXidFindCutOffReason()
> For now, I just added NOTICE messages which will log with
> VACUUM (verbose) for testing.
I like your idea , I think we also could consider introducing a GUC
parameter in the future, which would terminate sessions blocking vacuum
operations when the table's age reaches vacuum_failsafe_age.
Thanks
On Sat, Nov 15, 2025 at 8:25 AM Sami Imseih <samimseih@gmail.com> wrote:
> Thanks for starting this thread! This is a very useful
> feature that users will find beneficial to easily narrow
> down the reason the xmin horizon is being held back,
> and take action.
>
> Adding this information to the vacuum logging is useful, but
> I can see this information being exposed in a view as well in
> the future.
>
> I have a few comments:
>
> A few minor ones:
>
> 1/ pid should be declared as "pid_t"
>
> 2/ last value of an enum should be have a traling comma
> +typedef enum OldestXminSource
> +{
> + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> + OLDESTXMIN_SOURCE_OTHER
> +} OldestXminSource;
>
> More importantly:
>
> 3/ As mentioned earlier in the thread, the "idle-in-transaction"
> transactions is not being reported correctly, particularly for write
> tansactions. I think that is an important missing case. The reason
> for this is the cutoff xmin is not being looked up against the current
> list of xid's, so we are not blaming the correct pid.
>
> 4/
> Thinking about point 3 above, I began to wonder if this
> whole thing can be simplified with inspiration. Looking at the
> existing BackendXidGetPid(), I think it can.
>
> Based on BackendXidGetPid(), I tried a new routine called
> BackendXidFindCutOffReason() which can take in the cutoff xmin,
> passed in by vacuum and can walk though the proc array and
> determine the reason. We don't need to touch ComputeXidHorizons()
> to make this work, it seems to me. This comes with an additional
> walk though the procarray holding a shared lock, but I don't think
> this will be an issue.
>
> Attached is a rough sketch of BackendXidFindCutOffReason()
> For now, I just added NOTICE messages which will log with
> VACUUM (verbose) for testing.
>
> This takes what you are doing in v1 inside ComputeXidHorizons()
> into a new routine. I think this is a cleaner approach.
>
> 5/ Also, I think we should also include tests for serializable
> transactions
>
>
> What do you think?
>
> --
>
> Sami Imseih
> Amazon Web Services (AWS)
>
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, samimseih@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: <CAGjGUAK7mrwoQQYH=GtsfjTWRASACj1MY99iWQYZcacpMRxp5w@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