Re: sequencesync worker race with REFRESH SEQUENCES

On Fri, 10 Jul 2026 at 14:10, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> 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.

The attached v1-0001 patch avoids resetting the synchronization state
of sequences that are already being synchronized. Instead, it emits a
warning informing the user that the sequence is already being
synchronized and suggests rerunning ALTER SUBSCRIPTION ... REFRESH
SEQUENCES after the current synchronization completes. I'm not sure
whether a test is necessary for this change. Nevertheless, the
attached v1-0002 patch adds a regression test that uses an injection
point to deterministically reproduce the race condition and verifies
that the expected warning is emitted when ALTER SUBSCRIPTION ...
REFRESH SEQUENCES is invoked while a sequence synchronization is
already in progress.

Regards,
Vignesh