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>
In-Reply-To: <CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT=9RmiBkAxQkbQ@mail.gmail.com>
From: wenhui qiu <qiuwenhuifx@gmail.com>
Date: Thu, 22 Jan 2026 14:58:29 +0800
Message-ID: <CAGjGUAJ8JVL9J32ChchmVLgZycEyXf-r-KHc5EiRM3LDiEoPtA@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: Sami Imseih <samimseih@gmail.com>
Cc: Shinya Kato <shinya11.kato@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001f45dc0648f492af"
Archived-At: <https://www.postgresql.org/message-id/CAGjGUAJ8JVL9J32ChchmVLgZycEyXf-r-KHc5EiRM3LDiEoPtA%40mail.gmail.com>
Precedence: bulk

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

Hi Sami
> Alternatively, this information might be better exposed in a new system
> view, showing the "PID", "XID," and "reason" a transaction is blocking
VACUUM.
> This approach is more proactive as a DBA can continuously monitor for
> blocking reasons and take action before it becomes an issue.
Yes ,If this approach is acceptable, then as a reference for the standby
conflict-handling and query replay mechanism, we could consider introducing
a GUC parameter to terminate PID that blocks VACUUM freeze when the
relation age is approaching the vacuum_failsafe_age threshold.


Thanks

On Tue, Jan 6, 2026 at 6:02=E2=80=AFAM Sami Imseih <samimseih@gmail.com> wr=
ote:

> > Thank you all for the review comments, and sorry for the late reply.
> > I will address the review comments in order.
> >
> > On Sat, Nov 15, 2025 at 9:25=E2=80=AFAM Sami Imseih <samimseih@gmail.co=
m> wrote:
> > > 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 curren=
t
> > > 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.
> >
> > Thanks for the revised proposal! Your approach is clear and makes the
> > code easier to read.
>
> My approach is focused on correctness rather than simplicity.
>
> The current logic in ComputeXidHorizons can report the wrong PID for
> blocking transactions. For example:
>
> 1. start a pgbench with 5 clients for some time ( i.e. 5 minutes )
> ```
> pgbench -i -s50
> pgbench -c5 -T300
> ````
>
> 2. start a long running transaction that consumes an XID
> ```
> postgres=3D# begin;
> BEGIN
> postgres=3D*# SELECT txid_current(), pg_backend_pid();
>  txid_current | pg_backend_pid
> --------------+----------------
>       3665231 |         266601
> (1 row)
> ```
>
> 3. run a vacuum
> ```
> postgres=3D# vacuum verbose pgbench_accounts ;
> INFO:  vacuuming "postgres.public.pgbench_accounts"
> INFO:  finished vacuuming "postgres.public.pgbench_accounts": index scans=
:
> 0
> pages: 0 removed, 59833 remain, 59043 scanned (98.68% of total), 0
> eagerly scanned
> tuples: 0 removed, 1045578 remain, 35425 are dead but not yet removable
> removable cutoff: 4301694, which was 35981 XIDs old when operation ended
> oldest xmin source: active transaction (pid=3D267064)
> frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
> visibility map: 0 pages set all-visible, 0 pages set all-frozen (0
> were all-visible)
> index scan bypassed: 405 pages from table (0.68% of total) have 1494
> dead item identifiers
> avg read rate: 1320.425 MB/s, avg write rate: 2.404 MB/s
> buffer usage: 67745 hits, 49974 reads, 91 dirtied
> WAL usage: 1 records, 0 full page images, 299 bytes, 0 full page image
> bytes, 0 buffers full
> system usage: CPU: user: 0.22 s, system: 0.06 s, elapsed: 0.29 s
> VACUUM
> ```
>
> VACUUM reports the oldest-XID source PID as 267090 , but the correct
> PID is 267064. This happens because the ComputeXidHorizons loop picks
> the first PID it encounters with the cutoff XID, even if other backends
> have the same XID. There=E2=80=99s no reliable way within that loop to id=
entify
> the actual blocking transaction.
>
> ```
> postgres=3D# SELECT pid, datname, usename, state, backend_xmin,
> backend_xid, substr(query, 1, 20) as query
> FROM pg_stat_activity;
>   pid   | datname  | usename  |        state        | backend_xmin |
> backend_xid |        query
>
> --------+----------+----------+---------------------+--------------+-----=
--------+----------------------
>  267064 | postgres | postgres | active              |      4301694 |
>   4351291 | UPDATE pgbench_branc
>  267069 | postgres | postgres | active              |      4301694 |
>   4351332 | UPDATE pgbench_telle
>  267067 | postgres | postgres | active              |      4301694 |
>   4351299 | UPDATE pgbench_branc
>  267070 | postgres | postgres | active              |      4301694 |
>   4351279 | UPDATE pgbench_branc
>  267068 | postgres | postgres | active              |              |
>   4351325 | UPDATE pgbench_telle
>  267066 | postgres | postgres | active              |      4301694 |
>   4351327 | UPDATE pgbench_branc
>  267065 | postgres | postgres | active              |      4301694 |
>   4351292 | UPDATE pgbench_branc
>  267077 | postgres | postgres | active              |      4301694 |
>   4351303 | UPDATE pgbench_branc
>  266606 | postgres | postgres | active              |      4301694 |
>           | SELECT pid, datname,
>  267071 | postgres | postgres | active              |              |
>           | BEGIN;
>  267072 | postgres | postgres | active              |      4301694 |
>   4351300 | UPDATE pgbench_telle
>  267073 | postgres | postgres | active              |      4301694 |
>   4351258 | UPDATE pgbench_branc
>  267075 | postgres | postgres | idle                |              |
>           | END;
>  267074 | postgres | postgres | active              |      4301694 |
>   4351319 | UPDATE pgbench_branc
>  267076 | postgres | postgres | active              |              |
>   4351248 | END;
>  267084 | postgres | postgres | active              |      4301694 |
>   4351330 | UPDATE pgbench_telle
>  267078 | postgres | postgres | active              |      4301694 |
>   4351260 | UPDATE pgbench_branc
>  267082 | postgres | postgres | active              |              |
>   4351309 | END;
>  267081 | postgres | postgres | active              |              |
>   4351270 | UPDATE pgbench_branc
>  267083 | postgres | postgres | active              |              |
>   4351313 | END;
>  267080 | postgres | postgres | active              |      4301694 |
>   4351311 | UPDATE pgbench_branc
>  267079 | postgres | postgres | active              |      4301694 |
>   4351318 | UPDATE pgbench_branc
>  267086 | postgres | postgres | active              |      4301694 |
>   4351335 | UPDATE pgbench_branc
>  267085 | postgres | postgres | active              |              |
>           | BEGIN;
>  267090 | postgres | postgres | idle in transaction |              |
>   4301694 | SELECT txid_current( ************
> ```
>
> > - Your proposal incurs additional cost. Furthermore, the time lag
> > between the execution of ComputeXidHorizons() and
> > BackendXidFindCutOffReason() could lead to inaccurate logging.
>
> While scanning the proc array adds some overhead, it could be limited
> to cases where multiple VACUUMs are stuck on the same cutoff XID, but
> we will need to track the last cutoff-xmin to make that possible.
>
> Alternatively, this information might be better exposed in a new system
> view, showing the "PID", "XID," and "reason" a transaction is blocking
> VACUUM.
> This approach is more proactive as a DBA can continuously monitor for
> blocking reasons and take action before it becomes an issue.
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>

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

<div dir=3D"ltr">Hi=C2=A0Sami=C2=A0<div>&gt; Alternatively, this informatio=
n might be better exposed in a new system<br>&gt; view, showing the &quot;P=
ID&quot;, &quot;XID,&quot; and &quot;reason&quot; a transaction is blocking=
 VACUUM.<br>&gt; This approach is more proactive as a DBA can continuously =
monitor for<br>&gt; blocking reasons and take action before it becomes an i=
ssue.<br></div><div>Yes ,If this approach is acceptable, then as a referenc=
e for the standby conflict-handling and query replay mechanism, we could co=
nsider introducing a GUC parameter to terminate PID=C2=A0that blocks VACUUM=
 freeze when the relation age is approaching the vacuum_failsafe_age thresh=
old.<br></div><div><br></div><div><br></div><div>Thanks</div></div><br><div=
 class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Tue, Jan 6, 2026 at 6:02=E2=80=AFAM Sami Imseih &lt;<a href=3D"m=
ailto:samimseih@gmail.com">samimseih@gmail.com</a>&gt; wrote:<br></div><blo=
ckquote 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);paddi=
ng-left:1ex">&gt; Thank you all for the review comments, and sorry for the =
late reply.<br>
&gt; I will address the review comments in order.<br>
&gt;<br>
&gt; On Sat, Nov 15, 2025 at 9:25=E2=80=AFAM Sami Imseih &lt;<a href=3D"mai=
lto:samimseih@gmail.com" target=3D"_blank">samimseih@gmail.com</a>&gt; wrot=
e:<br>
&gt; &gt; More importantly:<br>
&gt; &gt;<br>
&gt; &gt; 3/ As mentioned earlier in the thread, the &quot;idle-in-transact=
ion&quot;<br>
&gt; &gt; transactions is not being reported correctly, particularly for wr=
ite<br>
&gt; &gt; tansactions. I think that is an important missing case. The reaso=
n<br>
&gt; &gt; for this is the cutoff xmin is not being looked up against the cu=
rrent<br>
&gt; &gt; list of xid&#39;s, so we are not blaming the correct pid.<br>
&gt; &gt;<br>
&gt; &gt; 4/<br>
&gt; &gt; Thinking about point 3 above, I began to wonder if this<br>
&gt; &gt; whole thing can be simplified with inspiration. Looking at the<br=
>
&gt; &gt; existing=C2=A0 BackendXidGetPid(), I think it can.<br>
&gt; &gt;<br>
&gt; &gt; Based on BackendXidGetPid(), I tried a new routine called<br>
&gt; &gt; BackendXidFindCutOffReason() which can take in the cutoff xmin,<b=
r>
&gt; &gt; passed in by vacuum and can walk though the proc array and<br>
&gt; &gt; determine the reason. We don&#39;t need to touch ComputeXidHorizo=
ns()<br>
&gt; &gt; to make this work, it seems to me. This comes with an additional<=
br>
&gt; &gt; walk though the procarray holding a shared lock, but I don&#39;t =
think<br>
&gt; &gt; this will be an issue.<br>
&gt; &gt;<br>
&gt; &gt; Attached is a rough sketch of BackendXidFindCutOffReason()<br>
&gt; &gt; For now, I just added NOTICE messages which will log with<br>
&gt; &gt; VACUUM (verbose) for testing.<br>
&gt;<br>
&gt; Thanks for the revised proposal! Your approach is clear and makes the<=
br>
&gt; code easier to read.<br>
<br>
My approach is focused on correctness rather than simplicity.<br>
<br>
The current logic in ComputeXidHorizons can report the wrong PID for<br>
blocking transactions. For example:<br>
<br>
1. start a pgbench with 5 clients for some time ( i.e. 5 minutes )<br>
```<br>
pgbench -i -s50<br>
pgbench -c5 -T300<br>
````<br>
<br>
2. start a long running transaction that consumes an XID<br>
```<br>
postgres=3D# begin;<br>
BEGIN<br>
postgres=3D*# SELECT txid_current(), pg_backend_pid();<br>
=C2=A0txid_current | pg_backend_pid<br>
--------------+----------------<br>
=C2=A0 =C2=A0 =C2=A0 3665231 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0266601<br>
(1 row)<br>
```<br>
<br>
3. run a vacuum<br>
```<br>
postgres=3D# vacuum verbose pgbench_accounts ;<br>
INFO:=C2=A0 vacuuming &quot;postgres.public.pgbench_accounts&quot;<br>
INFO:=C2=A0 finished vacuuming &quot;postgres.public.pgbench_accounts&quot;=
: index scans: 0<br>
pages: 0 removed, 59833 remain, 59043 scanned (98.68% of total), 0<br>
eagerly scanned<br>
tuples: 0 removed, 1045578 remain, 35425 are dead but not yet removable<br>
removable cutoff: 4301694, which was 35981 XIDs old when operation ended<br=
>
oldest xmin source: active transaction (pid=3D267064)<br>
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen<br>
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0<br>
were all-visible)<br>
index scan bypassed: 405 pages from table (0.68% of total) have 1494<br>
dead item identifiers<br>
avg read rate: 1320.425 MB/s, avg write rate: 2.404 MB/s<br>
buffer usage: 67745 hits, 49974 reads, 91 dirtied<br>
WAL usage: 1 records, 0 full page images, 299 bytes, 0 full page image<br>
bytes, 0 buffers full<br>
system usage: CPU: user: 0.22 s, system: 0.06 s, elapsed: 0.29 s<br>
VACUUM<br>
```<br>
<br>
VACUUM reports the oldest-XID source PID as 267090 , but the correct<br>
PID is 267064. This happens because the ComputeXidHorizons loop picks<br>
the first PID it encounters with the cutoff XID, even if other backends<br>
have the same XID. There=E2=80=99s no reliable way within that loop to iden=
tify<br>
the actual blocking transaction.<br>
<br>
```<br>
postgres=3D# SELECT pid, datname, usename, state, backend_xmin,<br>
backend_xid, substr(query, 1, 20) as query<br>
FROM pg_stat_activity;<br>
=C2=A0 pid=C2=A0 =C2=A0| datname=C2=A0 | usename=C2=A0 |=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 state=C2=A0 =C2=A0 =C2=A0 =C2=A0 | backend_xmin |<br>
backend_xid |=C2=A0 =C2=A0 =C2=A0 =C2=A0 query<br>
--------+----------+----------+---------------------+--------------+-------=
------+----------------------<br>
=C2=A0267064 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351291 | UPDATE pgbench_branc<br>
=C2=A0267069 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351332 | UPDATE pgbench_telle<br>
=C2=A0267067 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351299 | UPDATE pgbench_branc<br>
=C2=A0267070 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351279 | UPDATE pgbench_branc<br>
=C2=A0267068 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 4351325 | UPDATE pgbench_telle<br>
=C2=A0267066 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351327 | UPDATE pgbench_branc<br>
=C2=A0267065 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351292 | UPDATE pgbench_branc<br>
=C2=A0267077 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351303 | UPDATE pgbench_branc<br>
=C2=A0266606 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | SELECT pid, datname,<br>
=C2=A0267071 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | BEGIN;<br>
=C2=A0267072 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351300 | UPDATE pgbench_telle<br>
=C2=A0267073 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351258 | UPDATE pgbench_branc<br>
=C2=A0267075 | postgres | postgres | idle=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<b=
r>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | END;<br>
=C2=A0267074 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351319 | UPDATE pgbench_branc<br>
=C2=A0267076 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 4351248 | END;<br>
=C2=A0267084 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351330 | UPDATE pgbench_telle<br>
=C2=A0267078 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351260 | UPDATE pgbench_branc<br>
=C2=A0267082 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 4351309 | END;<br>
=C2=A0267081 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 4351270 | UPDATE pgbench_branc<br>
=C2=A0267083 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 4351313 | END;<br>
=C2=A0267080 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351311 | UPDATE pgbench_branc<br>
=C2=A0267079 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351318 | UPDATE pgbench_branc<br>
=C2=A0267086 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4301694 |<br>
=C2=A0 4351335 | UPDATE pgbench_branc<br>
=C2=A0267085 | postgres | postgres | active=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | BEGIN;<br>
=C2=A0267090 | postgres | postgres | idle in transaction |=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
=C2=A0 4301694 | SELECT txid_current( ************<br>
```<br>
<br>
&gt; - Your proposal incurs additional cost. Furthermore, the time lag<br>
&gt; between the execution of ComputeXidHorizons() and<br>
&gt; BackendXidFindCutOffReason() could lead to inaccurate logging.<br>
<br>
While scanning the proc array adds some overhead, it could be limited<br>
to cases where multiple VACUUMs are stuck on the same cutoff XID, but<br>
we will need to track the last cutoff-xmin to make that possible.<br>
<br>
Alternatively, this information might be better exposed in a new system<br>
view, showing the &quot;PID&quot;, &quot;XID,&quot; and &quot;reason&quot; =
a transaction is blocking VACUUM.<br>
This approach is more proactive as a DBA can continuously monitor for<br>
blocking reasons and take action before it becomes an issue.<br>
<br>
--<br>
Sami Imseih<br>
Amazon Web Services (AWS)<br>
</blockquote></div>

--0000000000001f45dc0648f492af--