Hi hackers, Attached is v4 of the patch adding a pg_current_vxact_id() function to PostgreSQL. Also please find thorough performance test results as Robert requested.
Changes in v4:
- Rebased onto current master (no functional changes)
- Addresses review comments from Henson Choi
== What the patch does ==
This patch introduces pg_current_vxact_id(), which exposes the current
backend's virtual transaction ID (VXID) as text in the format
'procNumber/localXID' (e.g., '3/42').
Virtual transaction IDs are always assigned to every backend, unlike
regular XIDs which are only assigned when a transaction modifies data.
This makes VXIDs useful for tracking and correlating all transactions,
including read-only ones, without consuming XID space.
The VXID format matches what is already used in:
- the %v placeholder in elog/log output
- the pg_locks.virtualtransaction column
- internal PostgreSQL transaction tracking
The function returns NULL during recovery or when no valid VXID exists.
== Performance ==
The existing workaround for getting one's own VXID is a scan of pg_locks:
SELECT virtualtransaction FROM pg_locks
WHERE pid = pg_backend_pid() AND locktype = 'virtualxid' LIMIT 1;
pg_locks scans the entire shared lock table (under LWLocks), so its cost
grows with the number of locks held system-wide. pg_current_vxact_id()
reads a single field from MyProc — O(1) with no lock acquisition.
Measured with pgbench on Linux x86-64, 16 CPUs (PostgreSQL 20devel).
Each run is 10 seconds; tps = transactions per second (higher is better).
Setup:
CREATE TABLE locktest (id int PRIMARY KEY, val text);
INSERT INTO locktest SELECT i, 'x' FROM generate_series(1, 10000) i;
pgbench scripts:
-- workaround (bench_pglocks.sql)
SELECT virtualtransaction FROM pg_locks
WHERE pid = pg_backend_pid() AND locktype = 'virtualxid' LIMIT 1;
-- new function (bench_vxact.sql)
SELECT pg_current_vxact_id();
Command:
pgbench -f bench_pglocks.sql -T 10 -c <N> -j <N> postgres
pgbench -f bench_vxact.sql -T 10 -c <N> -j <N> postgres
For the "with locks" scenario, 50 background sessions were opened each
holding an explicit transaction with SELECT ... FOR UPDATE on 200 rows
(keeping those transactions open for the duration of the benchmark run).
Baseline -- no extra locks held system-wide:
clients pg_locks workaround pg_current_vxact_id() speedup
1 5,991 tps 12,051 tps ~2x
8 37,221 tps 74,990 tps ~2x
16 82,132 tps 585,762 tps ~7x
With 50 background sessions each holding 200 row-locks (~10,000 locks
system-wide):
clients pg_locks workaround pg_current_vxact_id() speedup
1 4,559 tps 11,759 tps ~2.6x
8 28,338 tps 74,855 tps ~2.6x
16 51,893 tps 534,876 tps ~10x
The pg_locks workaround drops 24% at c=1 and 37% at c=16 as lock count
grows; pg_current_vxact_id() is unaffected (O(1), no LWLock). The gap
widens further with more clients due to LWLock contention on the shared
lock table.
== Why this approach ==
Rather than adding a new type, the function returns text in the canonical
'procNumber/localXID' format already established by pg_locks, so callers
can join directly against pg_locks.virtualtransaction without any casting.
A VXID_FMT macro was added to lock.h to eliminate the format-string
duplication that existed across lockfuncs.c, elog.c, and xid8funcs.c.
== Status ==
The patch compiles cleanly on Linux x86-64 and passes the full regression
test suite (make check). Regression tests covering the new function are
included in src/test/regress/sql/xid.sql. Documentation is in
func-info.sgml and xact.sgml.
Prior discussion:
https://www.postgresql.org/message-id/CAAAe_zBUd3epVqcDAMVmLDt4-dhxVY5W09%2Bgp5ND_P--b90eeA%40mail.gmail.com
Patch attached.
Regards,
Pavlo Golub
CYBERTEC PostgreSQL International GmbH
v4-0001-Add-pg_current_vxact_id-function.patch
Description: Binary data
