pgwire: no way to pin a read snapshot across pages — multi-page readers can observe half-applied producer batches; add pg_snapshot_xmin / SHOW SNAPSHOT #370

Version / build tested against

origin/main @ 1ff3551

Deployment mode

Origin, single node (local)

Engine(s) involved

pgwire

Summary

A reader that pages through a table with a keyset cursor takes a fresh snapshot per
page. When a producer writes in batches, page N can land between two of its
transactions and return a set that is internally inconsistent. A loader cannot tell the
difference between "the producer is still writing" and "that is all the data".

Consistent reads across pages is a normal database capability, and the cost here is
small: expose the current snapshot's xmin so a reader can hold a bound.

Steps to reproduce

No dedicated repro needed; it is a missing capability. Current state: there is no
pg_current_snapshot() or pg_snapshot_xmin(), and SHOW STATS is not a snapshot
handle. A keyset page therefore has no way to pin itself. The builtin function registry
is nodedb-sql/src/functions/builtins/scalars.rs, assembled by
builtin_functions in nodedb-sql/src/functions/builtins/all.rs:8, so the new function
has a clear home.

Expected behavior

One of these, whichever fits the function registry best:

SELECT pg_snapshot_xmin(pg_current_snapshot());
-- or
SHOW SNAPSHOT;

A reader captures the value once, then filters later pages with xmin <= that value
(or the equivalent visibility rule), so every page sees the same cut.

What actually happened? (check all that are true)

• Acknowledged/committed data was lost, corrupted, or silently wrong
• The server crashed, hung, or failed to start
• A security or isolation boundary was crossed
• Core functionality is broken with no acceptable workaround
• A workaround exists (a producer-side commit table, which pushes work onto writers)

Proposed severity

SEV-3, capability gap. The workaround costs every producer a run table and a discipline.

Reproducibility

Always, under any concurrent producer.

Last known-good version / commit (if a regression)

Not a regression.

Environment & logs

Linux x86_64, single-node origin, trust mode, psql 16.

Before submitting

• I searched existing issues and this is not a duplicate.
• I reproduced this on current main.
• This is not a security vulnerability.