Re: Logical Replication of sequences - Mailing list pgsql-hackers

On Sat, Oct 25, 2025 at 12:16 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Oct 24, 2025 at 11:43 AM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > 5)
> > For the race condition where the worker is going to access the seq
> > locally and meanwhile it is altered; now the worker correctly reports
> > this. But it reports this as a success scenario. And once the scenario
> > is reported as 'seq-worker finished', we do not expect it to start
> > running again without the user doing REFRESH. But in this case, it
> > runs, see logs. Also it starts immediately for once due to the same
> > reason that start_time is reset in the success scenario.
> > -------
> > 17:35:05.618 IST [132551] LOG:  logical replication apply worker for
> > subscription "sub1" has started
> > 17:35:05.637 IST [132553] LOG:  logical replication sequence
> > synchronization worker for subscription "sub1" has started
> > 17:35:05.663 IST [132553] LOG:  logical replication sequence
> > synchronization for subscription "sub1" - total unsynchronized: 1
> > 17:36:11.987 IST [132553] LOG:  skip synchronization of sequence
> > "public.myseq249" because it has been altered concurrently
> > 17:36:19.614 IST [132553] LOG:  logical replication sequence
> > synchronization for subscription "sub1" - batch #1 = 1 attempted, 0
> > succeeded, 1 skipped, 0 mismatched, 0 insufficient permission, 0
> > missing from publisher
> > 17:36:20.335 IST [132553] LOG:  logical replication sequence
> > synchronization worker for subscription "sub1" has finished
> > 17:36:20.435 IST [132586] LOG:  logical replication sequence
> > synchronization worker for subscription "sub1" has started
> > 17:36:20.545 IST [132586] LOG:  logical replication sequence
> > synchronization for subscription "sub1" - total unsynchronized: 1
> > -------
> >
> > The behaviour looks slightly odd. Is there anything we can do about
> > this? Shall the skipped case be reported as ERROR due to the fact that
> > we leave it in state 'i' in pg_subscription_rel?
> >
>
> The downside of reporting an ERROR as soon as we can't sync values for
> one of the sequences is that the other sequences which could be synced
> won't get synced.

Yes, I agree.

> The other possibility is that we skip processing
> such a sequence while copying sequences but at the end if there is any
> pending sequence which is not synced, we raise an ERROR. If we do that
> then we may need to give some generic ERROR because there could be
> multiple such sequences. The other possibility is that we can give a
> LOG message like "logical replication sequence sync worker for
> subscription \"%s\" will restart because ..." and then do proc_exit(1)
> without resetting restart_time. Will that help to address your
> concern?
>

Thanks for suggesting these potential solutions. After reviewing the
new approach proposed in [1] by Hou-San, I believe this race condition
is no longer applicable, so we’re safe.

[1]:
https://www.postgresql.org/message-id/TY4PR01MB169078D0BB792F1EE438A2EE294FCA%40TY4PR01MB16907.jpnprd01.prod.outlook.com

thanks
Shveta