Re: [PATCH] Support automatic sequence replication

On Fri, Feb 13, 2026 at 4:48 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> 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.
>
Yes. It is not present currently.

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

I agree with this. It will not be in scope of the current patch.

thanks
Shveta