Re: sequencesync worker race with REFRESH SEQUENCES

On Thu, Jul 16, 2026 at 8:28 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> vignesh C <vignesh21(at)gmail(dot)com> writes:
> > On Thu, 16 Jul 2026 at 06:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> That is, after checking for the "insufficient privileges" case,
> >> we aren't waiting long enough for the new seqsync worker to quiesce.
> >> This wasn't a problem before f38afa4ab, because it wasn't an error
> >> condition for that worker to still be running.
>
> > I think we can simply remove the REFRESH SEQUENCES command and add a
> > comment explaining that the sequence synchronization worker is
> > restarted automatically while there are sequences that are not yet in
> > the READY state.
>
> Well, that would fix this test, but I wonder if this result isn't
> telling us that f38afa4ab has created a failure condition that will
> bite real users. Why would the user issuing REFRESH SEQUENCES be
> aware of whether there's a sync worker running right then? Why
> should it be his problem to avoid that?
>

The reject is there for the correctness reason explained in the commit
(otherwise an in-flight worker can mark sequences READY with values it
fetched before the REFRESH, silently losing the refresh). But you
raised a fair point that it's not ideal to make the user mind worker
timing, but I think it will be uncommon in practice:

1. The sequencesync worker isn't a persistent process; it runs only
while some sequences are still non-READY, i.e. while a sync from
CREATE/REFRESH is initiated. Once all are READY, no worker runs and
REFRESH SEQUENCES just works.
2. While a worker is running, REFRESH SEQUENCES is largely redundant
anyway, as the running worker will itself bring those sequences to
READY with current values. It's genuinely needed only to re-pull newer
values for already-READY sequences, and in that state there's no
worker to conflict with.
3. REFRESH SEQUENCES isn't routine. Since ongoing sequence changes
aren't replicated, its primary use is to update sequences before a
planned switchover/failover/upgrade, and at that point the sequences
are normally already READY, so no worker is running.

The main exception is a sync that keeps failing and restarting (e.g.
missing publisher privileges), which is what skink (BF animal) hit,
but even then the user's goal is met once the underlying issue is
fixed, as the auto-restarting worker completes on its own. And the
command errors with a clear hint to retry after the sync completes.

--
With Regards,
Amit Kapila.