Re: Commit Sequence Numbers and Visibility

On Thu, 2026-08-27 at 14:21 +0500, Andrey Borodin wrote:
> My original interest in CSNs was the second problem, commonly called
> Long Fork[0].  The invariant I would like to get from this work is
> that
> every snapshot observable on a primary or physical standby is a
> prefix
> of one common transaction visibility order.

That's the problem I'm most interested in, as well. Postgres is not a
single-instance system, it has replicas, PITRs, etc. WAL is central to
all of that, and if we make a single point in WAL for both durability
and visibility, that greatly simplifies the overall system.

> T1 writes the
> earlier commit record and waits for remote_apply on a paused
> synchronous
> standby.  T2 then commits asynchronously and becomes visible on the
> primary.  A second standby applies T1 but is paused before T2. 

...

> Neither guarantee by itself defines the visibility order.  A commit-
> LSN
> CSN gives us the common prefix needed to prevent Long Fork, but
> exposes
> the hard case directly: a prefix containing T2 also contains T1 while
> T1 is still waiting for remote_apply.

I'm not clear on the use case where the reader needs to obey the
writer's durability setting. Why wouldn't the reader just care about
its own durability setting?

I can see how the writer has more of a sense of the importance of what
they are writing, but (at least for me) this is a theoretical argument.

> The Vancouver notes answer the question of a synchronous reader
> seeing an asynchronous commit by making the reader wait, but leave
> the
> reverse question unanswered: should an asynchronous reader see a
> synchronous commit that is still waiting?

Yes, assuming that the sync commit is in the reader's CSN snapshot. As
I proposed in the original email:

"wait until the CSN is flushed to the point that it meets the
durability requirements of the transaction using the snapshot"

In this case the sync transaction has already met the durability
requirements of the reader.

I'm open to the idea of a GUC that can control whether the reader waits
in that case, but that wouldn't affect visibility.

> Can we first agree on the common visibility order, and treat the
> durability required before a reader may consume a prefix as a
> separate
> policy?

If by "consume T1", you mean: "processes a query that includes T1's
changes and returns results to the client", then agreed, those are
separate concepts.

> More specifically, may a reader with a weak durability
> requirement consume T1 while T1's COMMIT is still waiting for
> remote_apply?

The reader can follow its own policy on whether to do so immediately or
wait for some durability requirement to be met first. If all
transactions have the same durability setting, then the durability
requirement has already been met by the time the CSN is assigned, so
waiting is unnecessary.

>   If not, does T1 keep T2 invisible despite T2 having
> already completed?

T1 will never keep T2 invisible; the visibility is defined only by the
CSN and commit LSNs of the transactions. If T2 commits and releases
locks, then any snapshot taken afterward will see both T2 and T1
(constraint (c)).

Waiting to use a snapshot is not the same as a transaction being
invisible to that snapshot.

> Distributed SSI seems like a separate and much larger project, rather
> than a prerequisite for CSN visibility.

Agreed.

>   I would be interested in
> helping with it, though its prospects look uncertain while the
> existing
> series of SSI bug fixes is still struggling to attract committer
> attention.

Perhaps CSN will simplify SSI in some ways, and make it easier to work
on going forward.

Regards,
Jeff Davis