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.
Nik
From cfce2e4cc9a78998702ef03e822d19fb8a3facb5 Mon Sep 17 00:00:00 2001
From: Nik Samokhvalov <[email protected]>
Date: Sun, 20 Sep 2026 23:00:52 -0700
Subject: [PATCH] Stop the sequencesync worker when a refresh removes
sequences.
ALTER SUBSCRIPTION ... REFRESH PUBLICATION, and SET/ADD/DROP PUBLICATION
with refresh, remove the pg_subscription_rel rows of sequences that are
no longer published. Unlike the table path, it did not stop a running
sequence synchronization worker. A worker that had already captured such
a sequence in its list of INIT sequences later failed in
UpdateSubscriptionRelState() with "subscription relation %u in
subscription %u does not exist" when marking the sequence READY. The
batch was rolled back and retried, sync_seq_error_count was incremented,
and with disable_on_error the whole subscription was disabled. The
worker had also already applied the publisher's value to the local
sequence, which is no longer subscribed.
Fix by stopping the sequence sync worker when removing a sequence, as is
done for tablesync workers and in AlterSubscription_refresh_seq(). This
is race-free for the same reason as there: the worker's catalog update
takes AccessShareLock on the subscription object, which
AlterSubscription() holds in AccessExclusiveLock mode until commit.
Add a test.
---
src/backend/commands/subscriptioncmds.c | 15 +++++
src/test/subscription/t/036_sequences.pl | 73 ++++++++++++++++++++++++
2 files changed, 88 insertions(+)
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 4f682cf0e..a22ec208f 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -1282,6 +1282,21 @@ AlterSubscription_refresh(Subscription *sub, bool copy_data,
RemoveSubscriptionRel(sub->oid, relid);
+ /*
+ * A sequence sync worker may already be running with this
+ * sequence in its to-do list. If it has fetched the value
+ * from the publisher but not yet marked the sequence READY,
+ * its UpdateSubscriptionRelState() would fail once we commit,
+ * as the row no longer exists. So stop the worker, as we do
+ * for tablesync workers above, and as
+ * AlterSubscription_refresh_seq() does. The worker's update
+ * is blocked by the AccessExclusiveLock on the subscription
+ * object until we commit, see AlterSubscription_refresh_seq()
+ * for why this is race-free.
+ */
+ logicalrep_worker_stop(WORKERTYPE_SEQUENCESYNC, sub->oid,
+ InvalidOid);
+
ereport(DEBUG1,
errmsg_internal("sequence \"%s.%s\" removed from subscription \"%s\"",
get_namespace_name(get_rel_namespace(relid)),
diff --git a/src/test/subscription/t/036_sequences.pl b/src/test/subscription/t/036_sequences.pl
index dd6fa515d..875ec14d2 100644
--- a/src/test/subscription/t/036_sequences.pl
+++ b/src/test/subscription/t/036_sequences.pl
@@ -290,6 +290,79 @@ $node_publisher->safe_psql(
));
# Wait for the recreated sequence to be synced.
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+##########
+# ALTER SUBSCRIPTION ... SET PUBLICATION (or REFRESH PUBLICATION) removing a
+# sequence must stop a running sequencesync worker, as it does for tablesync
+# workers. Otherwise the worker, which has already captured the sequence in
+# its to-do list, fails with an internal error when it tries to mark the
+# removed sequence as READY, and with disable_on_error the whole subscription
+# gets disabled.
+##########
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_seq_pub_empty");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION regress_seq_sub SET (disable_on_error = true)");
+
+# Block the sequencesync worker's batch query on the publisher, after the
+# worker has captured its list of sequences to sync.
+$pub_session = $node_publisher->background_psql('postgres');
+$pub_session->query_safe(
+ qq(
+ BEGIN;
+ DROP SEQUENCE regress_s1;
+));
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION regress_seq_sub REFRESH SEQUENCES");
+
+$node_publisher->poll_query_until(
+ 'postgres', qq(
+ SELECT EXISTS (
+ SELECT 1 FROM pg_locks
+ WHERE relation = 'regress_s1'::regclass
+ AND mode = 'AccessShareLock'
+ AND NOT granted);
+)) or die "timed out waiting for sequencesync worker to block on publisher";
+
+# Remove all sequences from the subscription while the worker is blocked.
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION regress_seq_sub SET PUBLICATION regress_seq_pub_empty"
+);
+
+# Let the worker continue.
+$pub_session->query_safe("ROLLBACK");
+$pub_session->quit;
+
+# Wait for the sequencesync worker to exit, then verify that the subscription
+# was not disabled.
+$node_subscriber->poll_query_until(
+ 'postgres', qq(
+ SELECT NOT EXISTS (
+ SELECT 1 FROM pg_stat_subscription
+ WHERE subname = 'regress_seq_sub'
+ AND worker_type = 'sequence synchronization');
+)) or die "timed out waiting for sequencesync worker to exit";
+
+is( $node_subscriber->safe_psql(
+ 'postgres',
+ "SELECT subenabled FROM pg_subscription WHERE subname = 'regress_seq_sub'"
+ ),
+ 't',
+ 'subscription stays enabled after sequences are removed during sync');
+
+# Restore the original publication and settings, and wait for the sequences
+# to be synced again.
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ ALTER SUBSCRIPTION regress_seq_sub SET (disable_on_error = false);
+ ALTER SUBSCRIPTION regress_seq_sub SET PUBLICATION regress_seq_pub;
+));
+
$node_subscriber->poll_query_until('postgres', $synced_query)
or die "Timed out while waiting for subscriber to synchronize data";
--
2.50.1 (Apple Git-155)
