Re: Commit Sequence Numbers and Visibility

On Sat, 13 Jun 2026 at 02:25, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Fri, 2026-06-12 at 12:03 +0200, Matthias van de Meent wrote:
>
> > > But we will have collapsed T1 and T2 into a single visibility
> > > event,
> > > which is technically a loss of information, and we'd need to sort
> > > through the nuances and implications.
> >
> > I don't think there are many nuances.
>
> How would remote_apply work? The docs say:
>
> "When set to remote_apply, commits will wait until replies from the
> current synchronous standby(s) indicate they have received the commit
> record of the transaction and applied it, so that it has become visible
> to queries on the standby(s), and also written to durable storage on
> the standbys."
>
> For it to have become visible remotely, the commit-visible record must
> also have been applied remotely. But the commit-visible record can't
> even be written locally until we've met the transaction's durability
> requirement. If remote_apply counts as a "durability requirement", then
> we have a cycle.

Yeah, that is something I hadn't yet taken into consideration. That
does disqualify remote_apply from being a requirement for VSN logging,
at least in its current form.

> [various problems]
> Perhaps these are not real problems or perhaps they have some
> resolution. Regardless, my main point is that we want to get away from
> the need to reason through this complexity. CSNs are simple to reason
> about, and that simplicity will pay off across all the dependent
> subsystems: replication, PITR, etc.

That is a good point. I agree CSNs are easier to reason about in those cases.

> > Adjusting (c) with durability waits when the snapshot encounters data
> > modified by not-yet-fully-durable transactions would just add this
> > option to the 'newly introduced waits' concern list, too.
>
> I'll think about this some more, it might eliminate most of the waits
> in practical cases.

I'm not sure it'll be sufficient for specific cases, but many cases,
probably, yes.
I think it's not weird to use async xacts for frequently updated rows
(counters), whose effects are then used to affect blindly-issued sync
xacts. It's not entirely unlike how sequences work right now, except
that sequences don't have MVCC.

- Matthias