Re: logical decoding and replication of sequences, take 2

On Tue, Jul 25, 2023 at 5:29 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> 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.
>
> IMHO the right fix is to make sure LSN3 >= LSN2 (for sequences).

Back in this thread, an approach to use page LSN (LSN2 above) to make
sure that no change before LSN2 is applied on subscriber. The approach
was discussed in emails around [1] and discarded later for no reason.
I think that approach has some merit.

[1] https://www.postgresql.org/message-id/flat/21c87ea8-86c9-80d6-bc78-9b95033ca00b%40enterprisedb.com#36bb9c7968b7af577dc080950761290d

--
Best Wishes,
Ashutosh Bapat