Re: sequencesync worker race with REFRESH SEQUENCES

On Thu, 16 Jul 2026 at 08:11, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Thu, 16 Jul 2026 at 06:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > BF member skink (uses valgrind) has failed
> > subscription/t/036_sequences.pl twice since f38afa4ab went in.
> > I can reproduce that locally if I run the postmaster under valgrind.
> > However, valgrind isn't issuing any complaint AFAICT. It looks like
> > valgrind simply slows things down enough to expose a race condition.
> > What is in my subscriber's log is
> >
> > ...
> > 2026-07-15 20:29:08.328 EDT client backend[3612746] 036_sequences.pl LOG: statement: ALTER SUBSCRIPTION regress_seq_sub REFRESH SEQUENCES
> > 2026-07-15 20:29:09.083 EDT logical replication sequencesync worker[3612758] LOG: logical replication sequence synchronization worker for subscription "regress_seq_sub" has started
> > 2026-07-15 20:29:10.095 EDT logical replication sequencesync worker[3612758] WARNING: insufficient privileges on publisher sequence ("public.regress_s2")
> > 2026-07-15 20:29:10.095 EDT logical replication sequencesync worker[3612758] HINT: Grant SELECT on the sequence to the role used for the replication connection on the publisher.
> > 2026-07-15 20:29:10.097 EDT logical replication sequencesync worker[3612758] ERROR: logical replication sequence synchronization failed for subscription "regress_seq_sub"
> > 2026-07-15 20:29:10.274 EDT postmaster[3612613] LOG: background worker "logical replication sequencesync worker" (PID 3612758) exited with exit code 1
> > 2026-07-15 20:29:10.501 EDT logical replication sequencesync worker[3612763] LOG: logical replication sequence synchronization worker for subscription "regress_seq_sub" has started
> > 2026-07-15 20:29:10.814 EDT client backend[3612766] 036_sequences.pl LOG: statement: ALTER SUBSCRIPTION regress_seq_sub REFRESH SEQUENCES
> > 2026-07-15 20:29:10.837 EDT client backend[3612766] 036_sequences.pl ERROR: cannot execute ALTER SUBSCRIPTION ... REFRESH SEQUENCES while a sequence synchronization worker is running
> > 2026-07-15 20:29:10.837 EDT client backend[3612766] 036_sequences.pl HINT: Try again after the current synchronization completes.
> > 2026-07-15 20:29:10.837 EDT client backend[3612766] 036_sequences.pl STATEMENT: ALTER SUBSCRIPTION regress_seq_sub REFRESH SEQUENCES
> >
> > That is, after checking for the "insufficient privileges" case,
> > we aren't waiting long enough for the new seqsync worker to quiesce.
> > This wasn't a problem before f38afa4ab, because it wasn't an error
> > condition for that worker to still be running.
>
> Thanks for reporting this. I was able to reproduce the issue in my
> environment as well, and I agree with your analysis of the root cause.
> Here's a breakdown of what is happening from the logs at [1]:
> Here, the sequence synchronization worker fails due to insufficient
> privileges on the publisher sequence, as shown in the following log:
> 2026-07-15 23:36:03.801 CEST [1681065][logical replication
> sequencesync worker][121/0:0] WARNING: insufficient privileges on
> publisher sequence ("public.regress_s2")
> 2026-07-15 23:36:03.801 CEST [1681065][logical replication
> sequencesync worker][121/0:0] HINT: Grant SELECT on the sequence to
> the role used for the replication connection on the publisher.
>
> The worker then exits with:
> 2026-07-15 23:36:03.813 CEST [1681065][logical replication
> sequencesync worker][121/0:0] ERROR: logical replication sequence
> synchronization failed for subscription "regress_seq_sub"
>
> Since not all sequences have reached the READY state, the sequence
> sync worker get restarted:
> 2026-07-15 23:36:05.523 CEST [1684606][logical replication
> sequencesync worker][122/6:0] LOG: logical replication sequence
> synchronization worker for subscription "regress_seq_sub" has started
>
> At nearly the same time, the test executes another ALTER SUBSCRIPTION
> ... REFRESH SEQUENCES, which fails because a sequence synchronization
> worker is already running:
> 2026-07-15 23:36:05.523 CEST [1684606][logical replication
> sequencesync worker][122/6:0] LOG: logical replication sequence
> synchronization worker for subscription "regress_seq_sub" has started
> 2026-07-15 23:36:07.081 CEST [1685689][client backend][31/2:0] LOG:
> statement: ALTER SUBSCRIPTION regress_seq_sub REFRESH SEQUENCES
> 2026-07-15 23:36:07.215 CEST [1685689][client backend][31/2:0] ERROR:
> cannot execute ALTER SUBSCRIPTION ... REFRESH SEQUENCES while a
> sequence synchronization worker is running
>
> The intent of this test is to verify the "insufficient privileges"
> error first, and then verify the behavior for missing sequences. Since
> the affected sequence has not yet reached the READY state, the apply
> worker will automatically launch a new sequence synchronization
> worker. Therefore, there is no need to issue an explicit ALTER
> SUBSCRIPTION ... REFRESH SEQUENCES in this test.
>
> I think we can simply remove the REFRESH SEQUENCES command and add a
> comment explaining that the sequence synchronization worker is
> restarted automatically while there are sequences that are not yet in
> the READY state.
>
> The attached patch has the changes for the same.

While reviewing this further, I noticed another scenario. After the
sequence synchronization worker marks all sequences as READY, if we
pause execution at report_sequence_errors(), an ALTER SUBSCRIPTION ...
REFRESH SEQUENCES can still fail. This means that checking
pg_subscription_rel and verifying that all sequences are in the READY
state is not sufficient. We also need to ensure that the sequence
synchronization worker itself has exited before proceeding, since it
may still be running even after updating the state to READY. I've
updated the test accordingly to include this additional check.
The attached v2 version patch has the changes for the same.

Regards,
Vignesh