Subject: Re: Commit Sequence Numbers and Visibility Hi Jeff, Matthias,
On Tue, Jun 2, 2026, Jeff Davis wrote: > some snapshots on the primary are impossible on the replica; and > it's inherently more expensive to acquire a new snapshot. I think it would help to keep three related goals separate here: 1. making snapshot acquisition scale better than copying xip[]; 2. making primary and standby snapshots prefixes of one common visibility order; 3. defining durability guarantees for the data admitted by a snapshot. 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. I attached a deterministic standalone TAP reproducer. 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. HEAD can then simultaneously return (T1 invisible, T2 visible) on the primary and (T1 visible, T2 invisible) on the standby. The test is not proposed for the recovery test schedule at this point. This is also a concrete instance of the mixed-durability sequence discussed above. I think we need to distinguish two guarantees: > wait until the CSN is flushed to the point that it meets the > durability requirements of the transaction using the snapshot rather > than the transaction writing the changes. - writer-selected durability says how persistent a transaction's changes must be before its COMMIT may return; - reader-selected durability says how persistent all data admitted by a snapshot must be before the reader may consume it. 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. 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? The reproducer makes the consequence of that choice concrete across a primary and standby. 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? More specifically, may a reader with a weak durability requirement consume T1 while T1's COMMIT is still waiting for remote_apply? If not, does T1 keep T2 invisible despite T2 having already completed? The same distinction matters when a synchronous replication wait is canceled after local commit, and when a recovered primary starts accepting clients before its end-of-recovery LSN reaches the synchronous standbys. Those are the two cases I have been working on in the "Small fixes needed by high-availability tools" thread[1]. On SERIALIZABLE, predicate-lock state is not WAL-logged, and SERIALIZABLE transactions are not supported on hot standbys today. Distributed SSI seems like a separate and much larger project, rather than a prerequisite for CSN visibility. 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. Thank you! Best regards, Andrey Borodin. [0] https://aws.amazon.com/blogs/database/understanding-transaction-visibility-in-postgresql-clusters-with-read-replicas/ [1] https://www.postgresql.org/message-id/flat/0B44E464-BA62-4056-9465-3320DD2D0302%40yandex-team.ru
long-fork-reproducer.diff
Description: Binary data
