MIME-Version: 1.0
References: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
In-Reply-To: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Tue, 3 Feb 2026 15:51:48 +0530
Message-ID: <CAJpy0uDLUEjHHME8om1vAf6qkXCeRR6cBvkpK8yWBAC=T0ZFLA@mail.gmail.com>
Subject: Re: [PATCH] Support automatic sequence replication
To: Ajin Cherian <itsajin@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, 
	shveta malik <shveta.malik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAJpy0uDLUEjHHME8om1vAf6qkXCeRR6cBvkpK8yWBAC%3DT0ZFLA%40mail.gmail.com>
Precedence: bulk

On Tue, Feb 3, 2026 at 9:18=E2=80=AFAM Ajin Cherian <itsajin@gmail.com> wro=
te:
>
> 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/5509055d6956745532e65ab=
218e15b99d87d66ce
>

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

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.

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"

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 =3D 3 attempted, 1 succeeded, 0 mismatched, 0 insufficient
permission, 2 missing from publisher, 0 skipped"

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?

thanks
Shveta