Re: [PATCH] Support automatic sequence replication - Mailing list pgsql-hackers

Hi,

On Tue, Feb 3, 2026 at 9:18 AM Ajin Cherian <itsajin@gmail.com> wrote:
>
> Hello hackers,
>
> I'd like to propose an improvement to the sequence replication feature
> that was committed in [1].
>
> The current implementation synchronizes sequences during initial
> subscription setup, but the sequence sync worker exits after this
> initial sync. This means that as sequences advance on the publisher,
> they drift from the subscriber values over time. Users must manually
> run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to resynchronize, which
> requires monitoring and intervention.
>
> Proposed Enhancement:
>
> This patch changes the sequence sync worker to run continuously
> throughout the subscription lifetime, automatically detecting and
> correcting sequence drift. The key changes are:
>
> 1. The sequence sync worker remains running instead of exiting after
> initial sync, periodically checking for and synchronizing drifted
> sequences.
>
> 2. The worker uses an exponential backoff strategy - starting at 2
> seconds, doubling up to a maximum of 30 seconds when sequences are in
> sync, and resetting to the minimum interval when drift is detected.
>
> 3. Since synchronization is now automatic, ALTER SUBSCRIPTION ...
> REFRESH SEQUENCES is no longer needed and has been removed.
>
> The patch modifies documentation to reflect the new behavior, removes
> the REFRESH SEQUENCES command from the grammar and subscription
> commands, and implements the continuous monitoring loop in
> sequencesync.c. Tap tests have been updated to verify automatic
> synchronization rather than manual refresh.
>
> The attached v2 patch is attached and ready for review.
>
> Thoughts and feedback are welcome!
>
> [1] - https://github.com/postgres/postgres/commit/5509055d6956745532e65ab218e15b99d87d66ce
>

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.

--