Re: Logical Replication of sequences

On Tue, 6 Aug 2024 at 09:28, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Tue, Aug 6, 2024 at 8:49 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
>
> Do we need some kind of coordination between table sync and sequence
> sync for internally generated sequences? Lets say we have an identity
> column with a 'GENERATED ALWAYS' sequence. When the sequence is synced
> to subscriber, subscriber can also do an insert to table (extra one)
> incrementing the sequence and then when publisher performs an insert,
> apply worker will blindly copy that row to sub's table making identity
> column's duplicate entries.
>
> CREATE TABLE color ( color_id INT GENERATED ALWAYS AS
> IDENTITY,color_name VARCHAR NOT NULL);
>
> Pub: insert into color(color_name) values('red');
>
> Sub: perform sequence refresh and check 'r' state is reached, then do insert:
> insert into color(color_name) values('yellow');
>
> Pub: insert into color(color_name) values('blue');
>
> After above, data on Pub: (1, 'red') ;(2, 'blue'),
>
> After above, data on Sub: (1, 'red') ;(2, 'yellow'); (2, 'blue'),
>
> Identity column has duplicate values. Should the apply worker error
> out while inserting such a row to the table? Or it is not in the
> scope of this project?

This behavior is documented at [1]:
Sequence data is not replicated. The data in serial or identity
columns backed by sequences will of course be replicated as part of
the table, but the sequence itself would still show the start value on
the subscriber.

This behavior is because of the above logical replication restriction.
So the behavior looks ok to me and I feel this is not part of the
scope of this project. I have updated this documentation section here
to mention sequences can be updated using ALTER SEQUENCE ... REFRESH
PUBLICATION SEQUENCES at v20240807 version patch attached at [2].

[1] - https://www.postgresql.org/docs/devel/logical-replication-restrictions.html
[2] - https://www.postgresql.org/message-id/CALDaNm01Z6Oo9osGMFTOoyTR1kVoyh1rEvZ%2B6uJn-ZymV%3D0dbQ%40mail.gmail.com

Regards,
Vignesh