Re: logical decoding and replication of sequences, take 2

On 7/26/23 09:27, Amit Kapila wrote:
> On Wed, Jul 26, 2023 at 9:37 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> ...
>>
>
> 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.
>

Well, I could argue that's more an opinion, and I'm not sure it really
contradicts the idea that the sequence should not go backwards only
after the sync completes.

Anyway, I was thinking about this a bit more, and it seems it's not as
difficult to use the page LSN to ensure sequences don't go backwards.
The 0005 change does that, by:

1) adding pg_sequence_state, that returns both the sequence state and
the page LSN

2) copy_sequence returns the page LSN

3) tablesync then sets this LSN as origin_startpos (which for tables is
just the LSN of the replication slot)

AFAICS this makes it work - we start decoding at the page LSN, so that
we skip the increments that could lead to the sequence going backwards.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company