Re: logical decoding and replication of sequences, take 2

On Wed, Jul 26, 2023 at 9:37 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Jul 25, 2023 at 5:29 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> >
> > On 7/25/23 08:28, Amit Kapila wrote:
> > > On Mon, Jul 24, 2023 at 9:32 PM Tomas Vondra
> > > <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> > >>
> > >> On 7/24/23 12:40, Amit Kapila wrote:
> > >>> On Wed, Jul 5, 2023 at 8:21 PM Ashutosh Bapat
> > >>> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > >>>
> > >>> Even after that, see below the value of the sequence is still not
> > >>> caught up. Later, when the apply worker processes all the WAL, the
> > >>> sequence state will be caught up.
> > >>>
> > >>
> > >> And how is this different from what tablesync does for tables? For that
> > >> 'r' also does not mean it's fully caught up, IIRC. What matters is
> > >> whether the sequence since this moment can go back. And I don't think it
> > >> can, because that would require replaying changes from before we did
> > >> copy_sequence ...
> > >>
> > >
> > > For sequences, it is quite possible that we replay WAL from before the
> > > copy_sequence whereas the same is not true for tables (w.r.t
> > > copy_table()). This is because for tables we have a kind of interlock
> > > w.r.t LSN returned via create_slot (say this value of LSN is LSN1),
> > > basically, the walsender corresponding to tablesync worker in
> > > publisher won't send any WAL before that LSN whereas the same is not
> > > true for sequences. Also, even if apply worker can receive WAL before
> > > copy_table, it won't apply that as that would be behind the LSN1 and
> > > the same is not true for sequences. So, for tables, we will never go
> > > back to a state before the copy_table() but for sequences, we can go
> > > back to a state before copy_sequence().
> > >
> >
> > Right. I think the important detail is that during sync we have three
> > important LSNs
> >
> > - LSN1 where the slot is created
> > - LSN2 where the copy happens
> > - LSN3 where we consider the sync completed
> >
> > For tables, LSN1 == LSN2, because the data is completed using the
> > snapshot from the temporary slot. And (LSN1 <= LSN3).
> >
> > But for sequences, the copy happens after the slot creation, possibly
> > with (LSN1 < LSN2). And because LSN3 comes from the main subscription
> > (which may be a bit behind, for whatever reason), it may happen that
> >
> > (LSN1 < LSN3 < LSN2)
> >
> > The the sync ends at LSN3, but that means all sequence changes between
> > LSN3 and LSN2 will be applied "again" making the sequence go away.
> >
>
> Yeah, the problem is something as you explained but an additional
> minor point is that for sequences we also do end up applying the WAL
> between LSN1 and LSN3 which makes it go backwards.
>

I was reading this email thread and found the email by Andres [1]
which seems to me to say the same thing: "I assume that part of the
initial sync would have to be a new sequence synchronization step that
reads all the sequence states on the publisher and ensures that the
subscriber sequences are at the same point. There's a bit of
trickiness there, but it seems entirely doable. The logical
replication replay support for sequences will have to be a bit careful
about not decreasing the subscriber's sequence values - the standby
initially will be ahead of the
increments we'll see in the WAL.". Now, IIUC this means that even
before the sequence is marked as SYNCDONE, it shouldn't go backward.

[1]: "https://www.postgresql.org/message-id/20221117024357.ljjme6v75mny2j6u%40awork3.anarazel.de

With Regards,
Amit Kapila.