Re: [PATCH] Support automatic sequence replication

Hi,

On Tue, Feb 3, 2026 at 9:18 AM Ajin Cherian <itsajin(at)gmail(dot)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: publicTABLE
NAME: t1CONSTRAINT 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.

--
With Regards,
Ashutosh Sharma.