Re: [PATCH] Support automatic sequence replication

On Fri, Feb 13, 2026 at 11:39 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> Is this expected behavior?
>
> 1) Publisher:
>
> create sequence t1_seq;
> create table t1 (id int default nextval('t1_seq') primary key, a int);
>
> create publication t1_pub for table t1;
> create publication t1_seq_pub for all sequences;
>
> 2) Subscriber:
>
> create sequence t1_seq;
> create table t1 (id int default nextval('t1_seq') primary key, a int);
>
> create subscription t1_sub connection 'host=127.0.0.1 port=37500 dbname=test user=$USER' publication t1_pub with (create_slot = false, slot_name = 't1_sub');
> create subscription t1_seq_sub connection 'host=127.0.0.1 port=37500 dbname=test user=$USER' publication t1_seq_pub with (create_slot = false, slot_name = 't1_seq_sub');
>
> select * from pg_subscription_rel;
> select * from pg_sequences;
>
> 3) Publisher:
>
> insert into t1(a) values(10);
> select * from pg_sequences;
>
> 4) Subscriber:
>
> select * from pg_sequences; -- in sync with publisher.
> insert into t1(a) values(20);
> select * from pg_sequences; -- the sequence gets deviated from publisher.
>
> After a few minutes, re-running the above shows that the sequence value is reset to match the publisher. However, any new insert on the subscriber fails:
>
> insert into t1(a) values(30);
> ERROR:  23505: duplicate key value violates unique constraint "t1_pkey"
> DETAIL:  Key (id)=(2) already exists.
> SCHEMA NAME:  public
> TABLE NAME:  t1
> CONSTRAINT NAME:  t1_pkey
>
> --
>
> Automatic sequence replication resets the last_value on the subscriber to match the publisher, which leads to duplicate key conflicts and prevents further inserts on the subscriber.
>

This is possible even without automatic sequence replication, say when
the user uses REFRESH SEQUENCES command just before values(30). This
is because sequence replication is mainly provided for upgrade
purposes where sequences can be made up-to-date before upgrade. We
should update this information in docs, if not already present.

Having said that, we can possibly detect such synchronization as the
sequence_update type of conflict and don't allow it to update on
subscribers but that will be a separate patch.

-- 
With Regards,
Amit Kapila.