public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ashutosh Sharma <[email protected]>
To: Ajin Cherian <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: [PATCH] Support automatic sequence replication
Date: Fri, 13 Feb 2026 11:38:55 +0530
Message-ID: <CAE9k0Pm+BtBTzzCuSi0KT3K9pSf2c9s4g-61KjAzmLUatXttBw@mail.gmail.com> (raw)
In-Reply-To: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
References: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>

Hi,

On Tue, Feb 3, 2026 at 9:18 AM Ajin Cherian <[email protected]> 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.

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: [PATCH] Support automatic sequence replication
  In-Reply-To: <CAE9k0Pm+BtBTzzCuSi0KT3K9pSf2c9s4g-61KjAzmLUatXttBw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox