MIME-Version: 1.0
References: <em5af9cba4-8cd3-4c9a-a593-c985d1c373ee@18a0083a.com>
 <CAAAe_zAjUwkKJzj8AGwzDx9CPxdwyyVYcMYZi-5jQX7HMWkkXw@mail.gmail.com> <em14322cd4-f84a-485d-b8d8-7dc2e2c58035@cybertec.at>
In-Reply-To: <em14322cd4-f84a-485d-b8d8-7dc2e2c58035@cybertec.at>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Fri, 9 Jan 2026 09:03:55 +0900
Message-ID: <CAAAe_zAJHcWVUD6RDpBWvo=dMwExqC2gXKsABNhRhwBE-s7DBw@mail.gmail.com>
Subject: Re: Re[2]: [PATCH] Add pg_current_vxact_id() function to expose
 virtual transaction IDs
To: Pavlo Golub <pavlo.golub@cybertec.at>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008ca1550647e9433f"
Archived-At: <https://www.postgresql.org/message-id/CAAAe_zAJHcWVUD6RDpBWvo%3DdMwExqC2gXKsABNhRhwBE-s7DBw%40mail.gmail.com>
Precedence: bulk

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

Hi Pavlo,

2026=EB=85=84 1=EC=9B=94 6=EC=9D=BC (=ED=99=94) PM 10:32, Pavlo Golub <pavl=
o.golub@cybertec.at>=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:

> Hello.
>
> Thanks for the review.
>
> >
> >Only pg_locks has it. And you can already get your VXID from there:
> >
> >   SELECT virtualtransaction FROM pg_locks
> >   WHERE pid =3D pg_backend_pid() LIMIT 1;
> While it is true that pg_locks contains the virtual transaction
> information, I believe there are strong technical reasons to expose this
> directly via a function.
>

Agreed.


> First of all, querying pg_locks is expensive. By contrast,
> pg_current_vxact_id() is a practically free O(1) read from MyProc.
>

Yes, this is a significant advantage. The function simply reads from
MyProc without any locking or iteration.


> The %v log placeholder is the specific identifier for individual
> transaction executions (including read-only ones where no permanent XID
> is assigned). PIDs (%p) are session-scoped and too coarse for helping
> debug specific transactions in connection-pooled environments. This
> function allows applications to easily obtain the ID needed to correlate
> with server logs.
>

This is a compelling use case. In connection-pooled environments,
correlating application-side logs with server logs by VXID is much
more precise than using PIDs.


> We already provide fast accessors for other identifiers like
> pg_backend_pid() and pg_current_xact_id(). Additionally, PostgreSQL
> often provides utility functions that overlap with other commands or
> views to improve developer experience (e.g., pg_notify() vs NOTIFY,
> pg_sleep() vs pg_sleep_for() vs pg_sleep_until()). It feels consistent
> to offer a simple accessor rather than requiring a complex query against
> a system view.
>
>
I agree. This follows established PostgreSQL patterns.


> Best regards,
> Pavlo Golub
>
>
Additionally, the implementation is minimal (~20 lines), so the binary
size impact is negligible. And since it's a leaf function called only
when explicitly invoked by users, it has no impact on the main code
path performance.

Best regards,
Henson

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Pavlo,<br></div><br><div class=3D"gmai=
l_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">2026=
=EB=85=84 1=EC=9B=94 6=EC=9D=BC (=ED=99=94) PM 10:32, Pavlo Golub &lt;<a hr=
ef=3D"mailto:pavlo.golub@cybertec.at">pavlo.golub@cybertec.at</a>&gt;=EB=8B=
=98=EC=9D=B4 =EC=9E=91=EC=84=B1:<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">Hello.<br>
<br>
Thanks for the review.<br>
<br>
&gt;<br>
&gt;Only pg_locks has it. And you can already get your VXID from there:<br>
&gt;<br>
&gt;=C2=A0 =C2=A0SELECT virtualtransaction FROM pg_locks<br>
&gt;=C2=A0 =C2=A0WHERE pid =3D pg_backend_pid() LIMIT 1;<br>
While it is true that pg_locks contains the virtual transaction <br>
information, I believe there are strong technical reasons to expose this <b=
r>
directly via a function.<br></blockquote><div><br></div><div>Agreed.<br></d=
iv><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-colo=
r:rgb(204,204,204);padding-left:1ex">
First of all, querying pg_locks is expensive. By contrast, <br>
pg_current_vxact_id() is a practically free O(1) read from MyProc.<br></blo=
ckquote><div><br></div>Yes, this is a significant advantage. The function s=
imply reads from<br>MyProc without any locking or iteration.<br><div>=C2=A0=
=C2=A0</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(20=
4,204,204);padding-left:1ex">
The %v log placeholder is the specific identifier for individual <br>
transaction executions (including read-only ones where no permanent XID <br=
>
is assigned). PIDs (%p) are session-scoped and too coarse for helping <br>
debug specific transactions in connection-pooled environments. This <br>
function allows applications to easily obtain the ID needed to correlate <b=
r>
with server logs.<br></blockquote><div><br></div><div>This is a compelling =
use case. In connection-pooled environments,<br>correlating application-sid=
e logs with server logs by VXID is much<br>more precise than using PIDs.<br=
></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-=
color:rgb(204,204,204);padding-left:1ex">
We already provide fast accessors for other identifiers like <br>
pg_backend_pid() and pg_current_xact_id(). Additionally, PostgreSQL <br>
often provides utility functions that overlap with other commands or <br>
views to improve developer experience (e.g., pg_notify() vs NOTIFY, <br>
pg_sleep() vs pg_sleep_for() vs pg_sleep_until()). It feels consistent <br>
to offer a simple accessor rather than requiring a complex query against <b=
r>
a system view.<br>
<br></blockquote><div><br></div><div>I agree. This follows established Post=
greSQL patterns.<br></div><div>=C2=A0</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">
Best regards,<br>
Pavlo Golub<br>
<br></blockquote><div><br></div>Additionally, the implementation is minimal=
 (~20 lines), so the binary<br>size impact is negligible. And since it&#39;=
s a leaf function called only<br>when explicitly invoked by users, it has n=
o impact on the main code<br>path performance.<br>=C2=A0<br>Best regards,<b=
r><div>Henson=C2=A0</div></div></div>

--0000000000008ca1550647e9433f--