Re: logical decoding and replication of sequences, take 2

On 7/25/23 15:18, Ashutosh Bapat wrote:
>
> ...
>
>> 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).
>

Do you agree this scheme would be correct?

> 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
>

That doesn't seem to be the correct link ... IIRC the page LSN was
discussed as a way to skip changes up to the point when the COPY was
done. I believe it might work with the scheme I described above too.

The trouble is we don't have an interface to select both the sequence
state and the page LSN. It's probably not hard to add (extend the
read_seq_tuple() to also return the LSN, and adding a SQL function), but
I don't think it'd add much value, compared to just getting the current
insert LSN after the COPY.

Yes, the current LSN may be a bit higher, so we may need to apply a
couple changes to get into "ready" state. But we read it right after
copy_sequence() so how much can happen in between?

Also, we can get into similar state anyway - the main subscription can
get ahead, at which point the sync has to catchup to it.

The attached patch (part 0007) does it this way. Can you try if you can
still reproduce the "backwards" movement with this version?

regards

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