On Tue, Sep 22, 2026 at 8:42 AM Nikolay Samokhvalov <[email protected]> wrote: > > On Fri, Jul 31, 2026, vignesh C <[email protected]> wrote: > > Here's the summary of the findings: > > Finding 1: Refresh paths race the in-flight sequencesync worker - > > committed (45cf7b1e5bf923ca48dfd9aa5001bdd0630d11c3) > > The PG19 open-items page still lists this item under "resolved before > 19beta3". I left that entry unchanged; please decide whether it should > be reopened. > > Part (B) of that finding is still there on REL_19_STABLE (b73d13c3) > and master (d39fda1c). 45cf7b1e5bf stops the worker in > AlterSubscription_refresh_seq(), but the sequence-removal loop in > AlterSubscription_refresh() still only takes AccessExclusiveLock on > pg_subscription_rel and calls RemoveSubscriptionRel(). Unlike the table > loop right above it, it does not stop the sequencesync worker. A worker > that already has the sequence in its INIT list fails once the refresh > commits: > > ERROR: subscription relation 16390 in subscription 16392 does not exist > > The batch is rolled back and sync_seq_error_count goes up. With > disable_on_error = true the whole subscription is disabled, tables > included. The worker has also already applied the publisher value to the > local sequence, which is no longer subscribed. > > Deterministic reproducer on REL_19_STABLE, no injection points: > > publisher: > create table t (id int primary key); > create sequence s1; create sequence s2; > create publication pub_seq for all sequences; > create publication pub_tab for table t; > > subscriber: > create table t (id int primary key); > create sequence s1; create sequence s2; > create subscription sub1 connection '...' publication pub_seq > with (disable_on_error = true, enabled = false); > > publisher, session A, keep it open: > begin; drop sequence s1; > > subscriber: > alter subscription sub1 enable; > -- wait until the sequencesync worker's batch query is blocked on > -- the publisher: pg_locks shows a not-granted AccessShareLock on s1 > alter subscription sub1 set publication pub_tab; > > publisher, session A: > rollback; > > subscriber, once the worker has exited: > select subenabled from pg_subscription; -- f > select sync_seq_error_count from pg_stat_subscription_stats; -- 1 > > The attached patch stops the sequencesync worker in the removal loop, > as the tablesync loop and AlterSubscription_refresh_seq() do, with the > same lock argument. It adds a test to 036_sequences.pl using the > publisher-side blocking trick that file already uses. The test fails on > unpatched REL_19_STABLE with the error above and the subscription > disabled, and passes with the fix. > > My AI harness found this while re-checking the fixes from this thread > and prepared the patch; I have not fully reviewed it by hand. With the > patch on b73d13c3 (cassert), the subscription TAP suite (39 files, 594 > tests) and the core regression suite (239 tests) pass. >
I had a look at the patch. The sequence sync worker is not per relid, unlike the tablesync worker. So stopping the sequence worker when the relid (one or a few) is removed is not quite the right analogy, since a single sequence worker could be handling a large number of sequences. Additionally, the same race condition can be reproduced by using the 'alter sub..refresh pub' command. The following test reproduces it using 'alter sub..refresh pub'. So whichever fix we decide on should address both the scenarios: Pub: create sequence s1; create sequence s2; create publication pub_seq for all sequences; Sub: create sequence s1; create sequence s2; create subscription sub1 connection '...' publication pub_seq with (disable_on_error = true, enabled = false); --pg_subscription_rel populated with both s1 and s2 in 'i' state. Pub's Session A: begin; alter sequence s2 rename to s2_tmp; Sub: alter subscription sub1 enable; --The worker started by above will wait on AccessExclusiveLock lock on pub while fetching seq s2 information. Pub's Session B: drop sequence s1; Sub: refresh while worker is still stuck on s2 on pub. alter subscription sub1 refresh publication; --This will remove s1's entry on sub from pg_subscription_rel Pub's Session A: rollback; Seq-worker proceeds and errors out as s1 is not found in pg_subscription_rel, it disables subscription [12132] ERROR: subscription relation 16384 in subscription 16386 does not exist thanks Shveta
