Re: Commit Sequence Numbers and Visibility

On Sat, 2026-08-29 at 23:46 +0500, Andrey Borodin wrote:
> One implementation question still seems open to me.  At Vancouver,
> Andres was concerned that eliminating Long Fork might require
> coupling
> WAL insertion with ProcArrayLock: the point where a transaction joins
> snapshot visibility must have a position in WAL, while no snapshot
> may
> pass that point without including the transaction.

I'd like to avoid that coupling, see below.

> Is maxTransactionFinishedPtr intended to avoid that interlock by
> making
> its atomic update the snapshot linearization point?  For example, if
> T1
> has inserted its commit record at LSN 1 but has not yet made its CSN
> state readable, and T2 advances maxTransactionFinishedPtr to LSN 2, a
> snapshot at LSN 2 must include T1 rather than treat it as in
> progress.
> Is the intended solution for that snapshot to wait on T1's commit-in-
> progress state, or do we need another WAL-visible visibility marker?

The snapshot acquisition wouldn't wait, but XidInMVCCSnapshot() would
need to wait if it encountered an xid that's still in a commit pending
state. I expect waiting would be rare, so a single global condition
variable should work.

The commit protocol would change to something like:

CSNLOG[xid] = COMMIT_PENDING
commitLSN = XLogInsert()
XLogSetAsyncXactLSN(commitLSN);
TransactionIdAsyncCommitTree(xid, ..., commitLSN);
CSNLOG[xid] = commitLSN
ConditionVariableBroadcast
if (sync)
XLogFlush(commitLSN)
advance_maxTransactionFinishedPtr(commitLSN)
ProcArrayEndTransaction()
release locks

Note that this uses a common path for sync and async transactions.
That's because we want to update CLOG and CSNLOG in memory so the
structures are consistent before we allow a lookup. But we also want to
allow lookups before the WAL flush, and that means we need the same
bookkeeping that the async path uses for CLOG updates. I believe that's
correct, but perhaps there's a good reason we haven't unified the paths
before?

> Is there any further design input you would like before
> implementation?

Wide [xmin,xmax) windows are a problem. I am working on a solution, but
I'd appreciate some input.

In the procarray world, anything between xmin and xmax is a lookup in
the snapshot's local xip array. In the CSN world, anything between xmin
and xmax must be checked in the shared CSNLOG, which is a major concern
if the window is wide.

I believe existing proposals try to solve this with caching in the
snapshot, but I don't think that's enough. Fundamentally, after
acquisition, the CSNLOG cannot distinguish two cases:

(i) a transaction that was running at or near the time the snapshot
was acquired; vs.
(ii) a transaction that briefly existed a long time before the
snapshot was acquired, but still has an xid greater than xmin.

so a cache over CSNLOG is not a great fit for solving that problem.
Perhaps some clever data structures can make that work in a lot of
cases, but I think it will be a source of pain.

The approach that I'm working on is to treat long-running transactions
specially. There would be a target threshold K, such that if a
transaction has xid < nextXid - K, then it's considered a long-running
transaction; otherwise it's a short-running transaction. K would be
some small multiple of the max concurrency.

Long-running transactions would be handled much like a procarray-based
snapshot handles all transactions today. We'd maintain a list of long-
running transactions in shared memory, and copy it into an xip-like
structure (LRxip) in the snapshot when the snapshot is acquired. Then
we filter out xids from LRxip where CSNLOG[xid] <= snap.CSN to maintain
CSN semantics.

The rationale is that capturing the distinction between (i) and (ii)
can only be done at snapshot acquisition time, and it's valuable
information. It allows us to have a new boundary (which I'm calling x0
for lack of a better name) such that:

case xid in:
[,xmin): included in snapshot
[xmin, x0): included in snapshot if xid not in LRxip
[x0, xmax): included in snapshot if CSNLOG[xid] <= snap.CSN
[xmax,): not included in snapshot

If there are long-running transactions, [xmin,x0) may be huge while
[x0,xmax) is limited to around K.

Would this approach get us right back into slow procarray-like
snapshots? I don't think so. I believe LRxip will be smaller and
cheaper to maintain than xip, and easy to optimize when there are zero
long-running transactions. I have more work to do here, of course, but
it feels like the right direction.

The main challenge will be to still try to win (or at least break even)
after years of optimization work on the existing procarray snapshots.

> Are you already planning to post a prototype?

Working on it actively, but don't have something ready yet.

>   If not, I can prepare
> one based on Heikki's latest "CSN snapshots in hot standby" patch. 
> Even
> if the interlock above is not fully settled, a prototype would make
> it
> concrete and let us measure its performance impact rather than reason
> about it only in the abstract.  I would initially focus on the common
> visibility order, the Long Fork reproducer, and the relevant
> concurrent
> workloads, leaving the reader durability policy as a separate step.

Having a patch that gets the semantics right and can act as a
performance baseline would be a great start.

Regards,
Jeff Davis