Horton

Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: shveta malik <shveta.malik@gmail.com>
Cc: Shlok Kyal <shlok.kyal.oss@gmail.com>, Chao Li <li.evan.chao@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Nisha Moond <nisha.moond412@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Peter Smith <smithpb2250@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, "Jonathan S. Katz" <jkatz@postgresql.org>
Date: 2025-09-30T16:25:34Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Add documentation for sequence synchronization.

  2. Remove unused assignment in CREATE PUBLICATION grammar.

  3. Add seq_sync_error_count to subscription statistics.

  4. Fix few issues in commit 5509055d69.

  5. Add sequence synchronization for logical replication.

  6. Add worker type argument to logical replication worker functions.

  7. Introduce "REFRESH SEQUENCES" for subscriptions.

  8. Refactor logical worker synchronization code into a separate file.

  9. Standardize use of REFRESH PUBLICATION in code and messages.

  10. Add "ALL SEQUENCES" support to publications.

  11. Expose sequence page LSN via pg_get_sequence_data.

  12. Resume conflict-relevant data retention automatically.

  13. Post-commit review fixes for 228c370868.

  14. Generate GUC tables from .dat file

Attachments

On Mon, 29 Sept 2025 at 09:59, shveta malik <shveta.malik@gmail.com> wrote:
>
> On Fri, Sep 26, 2025 at 12:55 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> > On Thu, 25 Sept 2025 at 12:23, shveta malik <shveta.malik@gmail.com> wrote:
> > >
> > > sequencesync_list_invalidate_cb():
> > > 5)
> > >
> > > + /* invalidate all entries */
> > > + hash_seq_init(&status, sequences_to_copy);
> > > + while ((entry = (LogicalRepSequenceInfo *) hash_seq_search(&status)) != NULL)
> > > + entry->entry_valid = false;
> > >
> > > Can you please elaborate when this case can be hit? I see such logic
> > > in all such invalidation functions registered with
> > > CacheRegisterRelcacheCallback(), but could not find any relevant
> > > comment.
> >
> > I noticed this could happen in cases like:
> > create publication for all tables;
> > alter publication on many relations;
> >
> > but there might be more apart from this
> >
>
> Okay. I will review more here.
>
> > Rest of the comments were addressed.
> > The attached patch has the changes for the same.
> >
>
> Thanks.
>
> I found a race condition between the apply worker and the sequence
> sync worker, where a sequence might be deleted from
> pg_subscription_rel and fail to be re-added when it should be.
>
> Steps:
>
> 1)
> The publisher and subscriber both have two sequences: seq1 and seq2.
>
> 2)
> A REFRESH PUBLICATION SEQUENCES command is executed on the subscriber.
> Before the sequencesync worker on the subscriber can locate the
> corresponding sequence on the publisher, the sequence gets dropped on
> the publisher. In other words, the sequence is removed from the
> publisher before walrcv_exec() is called in copy_sequences().
>
> 3)
> Before the sequencesync worker on the subscriber can drop the sequence
> locally, it is recreated on the publisher. Then, a second REFRESH
> PUBLICATION SEQUENCES command is executed on the subscriber. (i.e.,
> before RemoveSubscriptionRel() is reached in copy_sequences(), the
> sequence is already recreated on the publisher and a new refresh
> command is issued on the subscriber.)
>
> 4)
> During this second REFRESH PUBLICATION SEQUENCES, the sequence is
> found to already exist in pg_subscription_rel, so it is not re-added.
> However, concurrently, the sequencesync worker from the first refresh
> proceeds and drops the sequence from the subscriber.
>
> As a result, the sequence ends up being removed from
> pg_subscription_rel, even though it should have remained after both
> REFRESH PUBLICATION SEQUENCES commands.

I've resolved it by modifying the sequence sync worker to no longer
remove sequences from pg_subscription_rel, aligning its behavior with
that of the tablesync worker. This change ensures consistency and also
addresses the reported problem. The attached patch includes the
necessary modifications.

Regards,
Vignesh