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

First seen: 2025-12-08 12:09:58+00:00 · Messages: 15 · Participants: 4

Latest Update

2026-05-14 · claude-opus-4-6

Technical Analysis: pg_current_vxact_id() — Exposing Virtual Transaction IDs via SQL

Core Problem

PostgreSQL assigns every backend a Virtual Transaction ID (VXID) — a lightweight identifier composed of procNumber/localTransactionId — that uniquely identifies a transaction within the lifetime of a session. Unlike regular XIDs (which are only assigned to writing transactions and consume shared resources), VXIDs are universal: every transaction gets one, including read-only transactions that never need a permanent XID.

Despite being fundamental to PostgreSQL's internal transaction tracking, VXIDs have no direct SQL accessor. They appear in:

The only current way to retrieve your own VXID from SQL is an indirect query through the lock subsystem:

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

This is semantically confusing (querying locks to get a transaction identity), expensive (O(n) scan over all locks with tuple construction), and inconsistent with the API patterns established by pg_backend_pid() and pg_current_xact_id().

Proposed Solution

The patch adds a new built-in function pg_current_vxact_id() that performs a direct O(1) read from MyProc (the backend's PGPROC structure in shared memory) and returns the VXID as text in the format "procNumber/lxid" (e.g., "3/42").

Implementation Details

Evolution Across Versions

v1 (2025-12-08): Initial implementation with OID 5101.

v2 (2026-01-06):

v3 (2026-02-05):

v4 (pending): Minor pgindent comment reflow needed in xid8funcs.c.

Key Design Decisions and Tradeoffs

1. The "Convenience vs. Necessity" Debate

The central tension in this thread is whether the function provides enough value to justify its existence given that pg_locks already exposes the same information. Michael Paquier raised this directly:

"This is replacing one SQL in a given session by another... I don't see the need for this function, except simplicity in retrieving a session's state with less characters typed at the end?"

Pavlo's counter-arguments centered on three axes:

Robert Haas acknowledged the argument's validity but noted it's "all theoretical" — no one demonstrated a real-world scenario where the pg_locks approach was actually problematic. He expressed a general concern about accumulating functions "that can theoretically be justified but in reality get very little use."

2. Format String Consolidation (VXID_FMT)

Henson Choi's review identified that the VXID format string "%d/%u" was duplicated in three places. The v3 patch introduces:

#define VXID_FMT "%d/%u"

in lock.h, which all three consuming files already include transitively. This is a minor but positive improvement to maintainability — if the VXID format ever changes, there's now a single point of truth.

3. Documentation Terminology

An interesting inconsistency was surfaced: the C code uses localTransactionId (the actual struct field name, appearing 30+ times) while user-facing SGML documentation uses localXID (3 occurrences in xact.sgml and config.sgml). The patch adopts localXID for user-facing docs, which is the correct choice for audience-appropriate terminology.

4. OID Selection

The initial submission used OID 5101, which Michael Paquier flagged as not following the unused_oids script's guidance to use the 8000-9999 range during development (since OIDs get renumbered during beta anyway). This was corrected to 9538 in v2.

Architectural Significance

While this is a small patch, it touches on broader design philosophy questions:

  1. API completeness vs. minimalism: PostgreSQL has historically favored providing multiple access paths to the same information (e.g., NOTIFY/pg_notify(), SHOW/current_setting()). This patch extends that pattern to VXIDs.

  2. The VXID as a first-class concept: VXIDs are arguably more fundamental than XIDs — every transaction gets one, they're used for lock tracking, log correlation, and internal conflict resolution. Yet they lack the API surface that XIDs enjoy. This patch partially addresses that asymmetry.

  3. Connection pooling implications: In pooled environments where PIDs are reused across logical sessions, VXIDs provide more precise transaction-level correlation with server logs. The %v log placeholder exists precisely for this reason, but applications had no way to obtain the same identifier for self-correlation.

Current Status

As of the last message (2026-05-14), the patch is in a near-ready state: