Re: sequencesync worker race with REFRESH SEQUENCES

On Fri, Jul 10, 2026 at 12:30 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Fri, 10 Jul 2026 at 10:22, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >
> > A Fable 5 review of logical replication of sequences found a way to get
> > subscribed sequences into READY state despite the subscriber side having data
> > older than the last REFRESH SEQUENCES. I'm attaching the test case it wrote.
> > I reviewed the test, and I think it identifies a genuine defect.
>
> Thanks for reporting this. The test case reproduces this issue. This
> issue is not limited to lock contention. It can also occur if the
> sequence synchronization worker is simply slow. For example, the
> worker may fetch the sequence value from the publisher, after which
> the publisher sequence advances and ALTER SUBSCRIPTION ... REFRESH
> SEQUENCES is executed. When the worker eventually updates the
> subscriber, it uses the stale value that it fetched earlier,
> overwriting the sequence with an out-of-date value.

I have the same opinion here.

> How about raising a warning for the second REFRESH SEQUENCES
> indicating that the sequence is already being synchronized and
> skipping it? The warning could also include a hint to rerun ALTER
> SUBSCRIPTION ... REFRESH SEQUENCES after the current synchronization
> completes.

I agree. This is the simplest solution here.

~~

> Agree the basic approach that we avoid the re-initializing the catalog.
> My primitive idea is to follow what slotsync does. SlotSyncCtxStruct::syncing is
> checked from the SQL function and the slotsync worker, and either of them can
> continue. So we may be able to have LogicalRepCtxStruct::synching_seqs or
> something to indicate the status.
> But there might be other ways, needs to be investigated.

If this approach is accepted, I would prefer implementing it similarly
to how ALTER SUBSCRIPTION ... SET (two_phase) is handled. The command
simply fails (with an appropriate hint) if the apply worker for the
subscription is still running, by checking for the presence of the
apply worker.
IMO, a mechanism similar to SlotSync is not needed here because there
is currently no scenario in which continuous incremental
synchronization and one-time manual synchronization can run
concurrently. The worker itself is started only by the manual
synchronization operation and is not a continuously running process.

thanks
Shveta