MIME-Version: 1.0
References: <20260710045217.f0.noahmisch@microsoft.com>
 <CALDaNm2GBdszCTSJyiWrG244zzhwaNftqfXNT5YsCQsxJR22Dg@mail.gmail.com>
In-Reply-To: 
 <CALDaNm2GBdszCTSJyiWrG244zzhwaNftqfXNT5YsCQsxJR22Dg@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Fri, 10 Jul 2026 14:10:38 +0530
Message-ID: 
 <CAJpy0uDuHxBi3zYnXJmmjUND_0K4ROJ_dM96PY0PUJ8vtA7Ksw@mail.gmail.com>
Subject: Re: sequencesync worker race with REFRESH SEQUENCES
To: vignesh C <vignesh21@gmail.com>
Cc: Noah Misch <noah@leadboat.com>, amit.kapila16@gmail.com,
	pgsql-hackers@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/CAJpy0uDuHxBi3zYnXJmmjUND_0K4ROJ_dM96PY0PUJ8vtA7Ksw%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 10, 2026 at 12:30=E2=80=AFPM vignesh C <vignesh21@gmail.com> wr=
ote:
>
> On Fri, 10 Jul 2026 at 10:22, Noah Misch <noah@leadboat.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 havin=
g 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::syn=
cing 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