Re: sequencesync worker race with REFRESH SEQUENCES

On Fri, Jul 31, 2026, vignesh C <vignesh21(at)gmail(dot)com> 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.

Nik