Re: Logical Replication of sequences

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?

thanks
Shveta