Re: sequencesync worker race with REFRESH SEQUENCES

On Fri, Jul 17, 2026 at 12:14 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Jul 17, 2026 at 11:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Could we, on detecting the race condition problem, wait awhile
> > to see if it resolves?
> >
>
> Yes, we can do that. But it may not be completely reliable because
> sequencesync worker can exit and restart due to some ERROR. In those
> cases, again we can miss synchronizing the latest value of sequence.
>

Thinking some more on this idea, we can achieve to close the race
condition Noah reported without giving ERROR. The idea is that REFRESH
SEQUENCES can acquired lock on pg_subscription_rel in
AccessExclusiveLock to prevent a race with the apply worker
re-launching a sequence sync worker while we reset the states. Even if
a new worker starts, it can't progress: it opens pg_subscription_rel
in AccessShareLock mode in LogicalRepSyncSequences() and will block
until we commit, by which time the sequences are INIT and it will sync
the latest values. After acquiring, REFRESH SEQUENCES command can stop
the sequencesync worker and then made all sequence states to INIT. The
stop-and-restart design along with lock ensures that after REFRESH
SEQUENCES is finished all sequences will be guaranteed to have new
values. Attached patch implements this idea. I did basic testing and
it seems to be working but needs more review and test. I also want to
once review the decision of locking pg_subscription_rel.

Note: I'll think some more on the above idea and if I or someone
didn't find any hole in it then will revert f38afa4ab tomorrow and
then make this patch ready early next week.

--
With Regards,
Amit Kapila.