[PATCH] Add pg_current_vxact_id() function to expose virtual transaction IDs

I looked into where VXID is actually used:

SELECT c.relname, a.attname
FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid
WHERE a.attname LIKE '%virtual%' AND a.attnum > 0;

relname | attname
----------+--------------------
pg_locks | virtualxid
pg_locks | virtualtransaction

Only pg_locks has it. And you can already get your VXID from there:

SELECT virtualtransaction FROM pg_locks
WHERE pid = pg_backend_pid() LIMIT 1;

This always works since every transaction holds its own VXID lock.

For log correlation, PID works in most cases.

So I'm having trouble seeing a compelling use case. Could you share
a concrete scenario where this function would help?

The patch itself is clean, but I'm not sure about the justification.

I looked into where VXID is actually used:

SELECT c.relname, a.attname
FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid
WHERE a.attname LIKE '%virtual%' AND a.attnum > 0;

relname | attname
----------+--------------------
pg_locks | virtualxid
pg_locks | virtualtransaction

Only pg_locks has it. And you can already get your VXID from there:

SELECT virtualtransaction FROM pg_locks
WHERE pid = pg_backend_pid() LIMIT 1;

This always works since every transaction holds its own VXID lock.

For log correlation, PID works in most cases.

So I'm having trouble seeing a compelling use case. Could you share
a concrete scenario where this function would help?

The patch itself is clean, but I'm not sure about the justification.

2025년 12월 8일 (월) PM 9:10, Pavlo Golub <pavlo.golub@cybertec.at>님이 작성:

On Mon, Dec 08, 2025 at 12:09:58PM +0000, Pavlo Golub wrote:

Changes:
- OID 5101 (verified available with unused_oids script)

./unused_oids also states the following thing:
Best practice is to start with a random choice in the range 8000-9999.

We do an OID renumbering while in beta, so please use an OID in this
range for the development of new patches. ;)
--
Michael

Greetings!

Changes:
- OID 5101 (verified available with unused_oids script)

./unused_oids also states the following thing:
Best practice is to start with a random choice in the range 8000-9999.

Oh, I misunderstood it completely. I thought one should go to 8K+ if
they
want a block of OIDs not just one. Thanks!

We do an OID renumbering while in beta, so please use an OID in this
range for the development of new patches. ;)

Cool! Will update the patch. Thanks for your input!

Best regards,
Pavlo

Hello.

Attached is v2 of the pg_current_vxact_id() patch.
Changes in v2:
- Rebased on current master
- Changed OID from 5101 to 9538 (following unused_oids best practice
recommendation to use the 8000-9999 range for patch development)

Best regards,
Pavlo Golub

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 = 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.
First of all, querying pg_locks is expensive. By contrast,
pg_current_vxact_id() is a practically free O(1) read from MyProc.
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.
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.

Best regards,
Pavlo Golub

Hi Pavlo,

Thank you for the v2 patch. I've reviewed it and here are my comments:

== Summary ==

The patch applies cleanly and all regression tests pass.
The implementation is straightforward and follows existing patterns.

== Detailed Review ==

1. Functionality: OK
- The function correctly returns the VXID in the expected format.

2. Tests: OK
- Regression tests are included and pass.
- gcov/valgrind testing is unnecessary due to the simplicity of the code.

3. Code Safety: OK
- Buffer size (32 bytes) is sufficient for maximum output (23 bytes),
consistent with VXIDGetDatum() in lockfuncs.c.
- Memory allocated by cstring_to_text() via palloc is in
ecxt_per_tuple_memory and automatically managed.

4. Typos: None found.

== Suggestions for Improvement ==

1. Format String Duplication

The format string "%d/%u" is now duplicated in three places:
- src/backend/utils/adt/lockfuncs.c (VXIDGetDatum)
- src/backend/utils/error/elog.c (%v placeholder)
- src/backend/utils/adt/xid8funcs.c (pg_current_vxact_id)

Consider defining a macro in lock.h for consistency:

#define VXID_FMT "%d/%u"

All three files already include lock.h indirectly:
- lockfuncs.c -> predicate_internals.h -> lock.h
- elog.c -> proc.h -> lock.h
- xid8funcs.c -> proc.h -> lock.h

2. Documentation Terminology

The terms "localTransactionId" and "localXID" are used inconsistently:
- localTransactionId: 30+ in C code (actual field name), 1 in sgml
(monitoring.sgml)
- localXID: 3 in sgml only (xact.sgml, config.sgml)

The new func-info.sgml uses "localTransactionId" which matches the
actual C struct field name. However, existing documentation prefers
"localXID" for user-facing text. Consider using "localXID" in
func-info.sgml for consistency with xact.sgml and config.sgml.

== Comparison with pg_current_xact_id ==

The implementation follows a similar pattern to pg_current_xact_id(),
which was introduced in commit 4c04be9b05a. The placement in
xid8funcs.c is appropriate.

--

2026년 1월 6일 (화) PM 9:47, Pavlo Golub <pavlo.golub@cybertec.at>님이 작성:

Hello.

Attached is v2 of the pg_current_vxact_id() patch.
Changes in v2:
- Rebased on current master
- Changed OID from 5101 to 9538 (following unused_oids best practice
recommendation to use the 8000-9999 range for patch development)

Best regards,
Pavlo Golub

Best regards,
Henson

Hi Pavlo,

2026년 1월 6일 (화) PM 10:32, Pavlo Golub <pavlo.golub@cybertec.at>님이 작성:

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 = 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

Hi Pavlo,

I've moved this patch to "Waiting on author" status in the commitfest.

I'm interested in your thoughts on the two suggestions from my review:

1. VXID_FMT macro to eliminate format string duplication
2. Using "localXID" terminology in documentation for consistency

Would you like to incorporate these in v3, or do you have concerns
about either suggestion?

Looking forward to your feedback.

Best regards,
Henson Choi

2026년 1월 6일 (화) PM 10:59, Henson Choi <assam258@gmail.com>님이 작성:

On Mon, Dec 08, 2025 at 12:09:58PM +0000, Pavlo Golub wrote:

Virtual transaction IDs are fundamental to PostgreSQL's transaction
tracking,
appearing in pg_locks.virtualtransaction, log output via %v placeholder, and
internal transaction management. However, there's currently no direct SQL
function to retrieve the current VXID, forcing applications to query
pg_locks
or parse log files to obtain this information.

This is replacing one SQL in a given session by another, as a session
currently running a transaction can query itself pg_locks and match an
entry with its own pg_backend_pid(). Hence I don't see the need for
this function, except simplicity in retrieving a session's state with
less characters typed at the end?

Thoughts and opinions from others are welcome. I'm always OK to be
outvoted.
--
Michael

2026년 1월 9일 (금) AM 9:25, Michael Paquier <michael@paquier.xyz>님이 작성:

On Mon, Dec 08, 2025 at 12:09:58PM +0000, Pavlo Golub wrote:

Virtual transaction IDs are fundamental to PostgreSQL's transaction
tracking,
appearing in pg_locks.virtualtransaction, log output via %v placeholder,

and

internal transaction management. However, there's currently no direct SQL
function to retrieve the current VXID, forcing applications to query
pg_locks
or parse log files to obtain this information.

This is replacing one SQL in a given session by another, as a session
currently running a transaction can query itself pg_locks and match an
entry with its own pg_backend_pid(). Hence I don't see the need for
this function, except simplicity in retrieving a session's state with
less characters typed at the end?

I see this as a tradeoff between minor convenience and negligible
addition cost.

The community should decide whether this tradeoff is worth it.

Thoughts and opinions from others are welcome. I'm always OK to be
outvoted.
--
Michael

Best regards,
Henson Choi

Hi hackers,

Thank you a lot for the feedback on this patch. And sorry for the
delay with the answer.

I'd like to address Michael's concern that pg_current_vxact_id() is
merely a convenience feature when pg_locks can provide the same
information. I believe there are stronger technical and design reasons
for this function beyond reducing typing.

pg_current_vxact_id() complexity is O(1) with direct memory reads and
no locks, while querying pg_locks requires O(n) where n is the number
of locks, among these operations are constructing tuples then
filtering. For me it's obviously more work. The proposed function is
essentially free compared to pg_locks querying.

As for the usage pattern this is how Postgres codebase is built from
the very beginning. PostgreSQL has many established patterns where
both SQL commands and functions exist for the same purpose, providing
programmatic access even when an alternative exists, e.g. NOTIFY vs
pg_notify(), SHOW vs current_setting(), pg_stat_activity vs
pg_current_xact_id() vs pg_current_xact_id_if_assigned(),
pg_stat_activity vs pg_backend_pid(), etc.

I see a clear pattern where Postgres provides function APIs fir
frequently needed info, even if it's theoretically possible to get
that info through other means.

My main concern though is about semantic clarity. I see a huge problem
that one needs to query pg_locks to get VXID. Why would I want to
query the lock subsystem to get transaction ID? That's very confusing.

pg_current_vxact_id() is semantically precise: "What is my
transaction's VXID?" We immediately have the direct answer from MyProc
without indirection through unrelated subsystems, which is pretty
obvious to anyone reading the code.

Having pg_current_vxact_id() increases API consistency. VXIDs deserve
the same treatment because they're fundamental to Postgres transaction
tracking and they uniquely identify every transaction (unlike XIDs). I
could day VXIDs are universal and that's why I think the current
asymmetry is odd:
- get XID: SELECT pg_current_xact_id()
- get PID: SELECT pg_backend_pid()
- get VXID: SELECT ... FROM pg_locks WHERE ... LIMIT 1 (why?)

Plus taking into account the minimal implementation and support costs
we should apply it. 20 lines of code without new infrastructure or
data structures changes sounds like a solid and maintainable piece. No
runtime overhead, no maintenance burden, simplest function without
locks or side effects.

I've prepared v3 of the patch addrressing Henson's code review:
- Added #define VXID_FMT "%d/%u" to lock.h
- Updated lockfuncs.c, elog.c, and xid8funcs.c to use it
- Use "localXID" (not "localTransactionId") in user docs

I'd appreciate thoughts from others in the community on whether this
reasoning is compelling.

Best regards,
Pavlo Golub

On Thu, Feb 5, 2026 at 8:13 AM Pavlo Golub <pavlo.golub@cybertec.at> wrote:

pg_current_vxact_id() complexity is O(1) with direct memory reads and
no locks, while querying pg_locks requires O(n) where n is the number
of locks, among these operations are constructing tuples then
filtering. For me it's obviously more work. The proposed function is
essentially free compared to pg_locks querying.

I agree with this and would be inclined to accept the patch. I have
reviewed the v3 patch and I didn't see anything wrong with it. I think
it could be committed if we were not in feature freeze, and of course
provided that we had consensus.

I'd appreciate thoughts from others in the community on whether this
reasoning is compelling.

I think the only thing I find slightly less than compelling about this
reasoning is that it's all theoretical. If you said, look, we tried
the pg_locks method in our environment and the performance was
demonstrably bad, and then we wrote an extension that does the same
thing as this function and then the performance was good, that would
be a much stronger argument in my mind. Otherwise, someone might
hypothesize that the performance gap that you point out here, while
almost certainly read in and of itself, is not something that anyone
has a real-world reason to care about. I do sometimes have concerns
about adding a whole bunch of functions to the default install that
can theoretically be justified but in reality get very little use. In
general I think the bar for adding stuff like this should be fairly
low, but I also wonder how many people would really be sad if we were
missing, e.g., asinh() or pg_get_tablespace_ddl() or pg_sleep_until(),
just to pick a few examples of things that have failed to win my
unqualified confidence over the years.

I'm not saying any of those were necessarily bad things for us to
accept; the use case isn't nothing, and asinh() is apparently even in
the SQL standard. At the same time, I bet the percentage of installs
that will ever use any of them is real small.

--
Robert Haas
EDB: http://www.enterprisedb.com

Hi Pavlo, Robert,

Responding to both of you in one mail since the points are related.

== To Pavlo, on v3 ==

On Thu, Feb 5, 2026 at 10:13 AM Pavlo Golub <pavlo.golub@cybertec.at> wrote:

I've prepared v3 of the patch addrressing Henson's code review:
- Added #define VXID_FMT "%d/%u" to lock.h
- Updated lockfuncs.c, elog.c, and xid8funcs.c to use it
- Use "localXID" (not "localTransactionId") in user docs

Thanks. All three items from my v2 review are addressed in v3:

1. VXID_FMT macro -- OK
2. VXID_FMT applied to 3 files -- OK
3. "localXID" in func-info.sgml -- OK

The patch applies cleanly on master and "make check-world" passes on
my machine (macOS/arm64).

My main concern though is about semantic clarity. I see a huge problem
that one needs to query pg_locks to get VXID. Why would I want to
query the lock subsystem to get transaction ID? That's very confusing.

Agreed. The asymmetry with pg_backend_pid() / pg_current_xact_id() is
the part I find most persuasive too -- regardless of performance,
having to enter the lock subsystem to ask "what is my transaction's
identity?" is an odd shape for the API.

== To Robert ==

On Sat, Feb 14, 2026 at 2:16 AM Robert Haas <robertmhaas@gmail.com> wrote:

I agree with this and would be inclined to accept the patch. I have
reviewed the v3 patch and I didn't see anything wrong with it.

Thanks for taking a look. I'll leave the empirical side of the
performance argument to Pavlo if he wants to follow up on it; my own
endorsement rests mainly on the API-shape point above.

== One nit on v3 ==

Running src/tools/pgindent/pgindent over the touched C/H files
produces one small rewrap in xid8funcs.c (no semantic change, just a
comment reflow):

    - * Check if we have a valid vxid.  The vxid format matches what's used
    - * in elog.c for the %v placeholder and in pg_locks.virtualtransaction.
    + * Check if we have a valid vxid.  The vxid format matches what's used
in
    + * elog.c for the %v placeholder and in pg_locks.virtualtransaction.

Pavlo, could you fold that into a v4? Other than that I have nothing
more to add, and with v4 in place I would mark the patch Ready for
Committer.

Best regards,
Henson Choi

Hi all,

I went through the discussion and tested the patch on my current
branch and I wanted to share a few observations based on my findings.
One of the concerns raised was whether this is primarily a convenience
wrapper around existing functionality via pg_locks. While it is
technically possible to retrieve the VXID from
pg_locks.virtualtransaction, the behavior in practice is not
equivalent. pg_locks exposes lock-level entries (a single transaction
can appear multiple times), requiring additional filtering or
deduplication. In that sense, it is not a direct representation of
transaction identity. While it is technically possible to obtain the
VXID via pg_locks, that approach is indirect and tied to lock-level
representation. Providing a dedicated function exposes an already
existing transaction identifier in a way that is consistent, reliable,
and appropriate for its semantics. From the testing, what stood out
clearly is that VXID is the only identifier that is consistently
available across all transaction states. In read-only transactions and
before any write, pg_current_xact_id_if_assigned() returns NULL, while
VXID is already present and stable. Even after XID assignment, VXID
remains unchanged throughout the transaction lifetime. This makes VXID
fundamentally different from XID in terms of observability and also
the only identifier that exists uniformly for all transactions.
Providing a direct SQL interface, compared to accessing it via
pg_locks or log_line_prefix (%v), seems consistent with existing
exposure patterns. So from a usability and observability point of
view, this will be like adding a convenience function and more like
filling a small but real gap in the SQL interface.
Overall, the patch behavior is consistent across transaction states
and the patch looks good to me.

Regards,
Solai