On Fri, Jul 17, 2026 at 5:23 PM Amit Kapila <[email protected]> wrote: > > 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. >
I have pushed the revert. Attached patch fixes the race without taking lock on pg_subscription_rel. This should be race-free because AlterSubscription() already holds AccessExclusiveLock on the subscription object. That lock blocks a running worker's UpdateSubscriptionRelState(), which takes AccessShareLock on the object, and also any worker the apply worker re-launches, because a new worker takes AccessShareLock on the object in InitializeLogRepWorker() before it reads pg_subscription_rel. Such a worker cannot act on the sequence states until the refresh commits, by which time they are reset to INIT and it will synchronize the latest publisher values. -- With Regards, Amit Kapila.
v2-0001-Handle-concurrent-sequence-refreshes.patch
Description: Binary data
