Subject: Re: [PATCH] Support automatic sequence replication

From: Ajin Cherian <[email-masked]>
Date: Thu, 5 Feb 2026 16:03:13 +1100

On Tue, Feb 3, 2026 at 9:22 PM shveta malik <[email-masked]> wrote:
>
> On Tue, Feb 3, 2026 at 9:18 AM Ajin Cherian <[email-masked]> wrote:
> Thanks for the patch.
>
> +1 for the overall idea of patch that once a subscription is created
> which subscribes to sequences, a sequence sync worker is started which
> continuously syncs the sequences. This makes usage of REFRESH
> SEQUENCES redundant and thus it is removed. I am still reviewing the
> design choice here, and will post my comments soon (if any).
>

Thanks!

> By quick validation, few issues in current implementation:
>
> 1)
> If the sequence sync worker exits due to some issue (or killed or
> server restarts), sequence-sync worker is not started again by apply
> worker unless there is a sequence in INIT state i.e. synchronization
> of sequences in READY state stops. IIUC, the logic of
> ProcessSequencesForSync() needs to change to start seq sync worker
> irrespective of state of sequences.
>

Yes, I fixed this. I've changed FetchRelationStates to fetch sequences
in ANY state and not just ones in NON READY state.

> 2)
> There is some issue in how LOGs (DEBUGs) are getting generated.
>
> a) Even if there is no drift, it still keeps on dumping:
> "logical replication sequence synchronization for subscription "sub1"
> - total unsynchronized: 3"
>

Removed this.

> b)
> When there is a drift in say single sequence, it puts rest (which are
> in sync) to "missing" section:
> "logical replication sequence synchronization for subscription "sub1"
> - batch #1 = 3 attempted, 1 succeeded, 0 mismatched, 0 insufficient
> permission, 2 missing from publisher, 0 skipped"
>

Fixed, and added a new section called "no drift". Also now this debug
message is printed every time the worker attempts to synchronize
sequences. also mentioning the state of the sequences being synced.

> 3)
> If a sequence sync worker is taking a nap, and subscription is
> disabled or the server is stopped just before upgrade, how is the user
> supposed to know that sequences are synced at the end?

Well, one way is to wait for a debug message that says that all the
attempted sequences are in the "no drift" state. Also remote
sequence's LSN is updated in pg_subscription_rel for each sequence.
Let me know if you have anything more in mind. One option is to leave
the ALTER SUBSCRIPTION..REFRESH SEQUENCE in place, that will change
the state of all the sequences to the INIT state, and the user can
then wait for the sequences to change state to READY.

Attaching patch v3 addressing the above comments.

regards,
Ajin Cherian
Fujitsu Australia

---

From: Peter Smith <[email-masked]>
Date: Fri, 6 Feb 2026 18:38:55 +1100

Hi Ajin.

Some review comments for patch v2-0001.

======
.../replication/logical/sequencesync.c

copy_sequences:

1.
+ return drift_detected;

This seems a bit strange. And it is not doing quite what the function
comment says it does. I felt you should have another variable like
'sequences_copied', which is set to true only when that
'batch_succeeded_count++' is incremented. This is what you ultimately
want to return. IMO, the variable 'drift_detected' isn't needed at
all.

======
src/test/subscription/t/036_sequences.pl

2.
##########
## ALTER SUBSCRIPTION ... REFRESH PUBLICATION should cause sync of new
# sequences of the publisher.
##########

# Create a new sequence 'regress_s2', and update existing sequence 'regress_s1'
$node_publisher->safe_psql(5.
'postgres', qq(
CREATE SEQUENCE regress_s2;
INSERT INTO regress_seq_test SELECT nextval('regress_s2') FROM
generate_series(1,100);

-- Existing sequence
INSERT INTO regress_seq_test SELECT nextval('regress_s1') FROM
generate_series(1,100);
));

~

IIUC, you are no longer sync of testing "existing sequences" in this
test part, so you might also want to remove that comment and INSERT
for 'regress_s1'.

======
Kind Regards,
Peter Smith.
Fujitsu Australia