Horton

Re: Logical Replication of sequences

shveta malik <shveta.malik@gmail.com>

View thread
From: shveta malik <shveta.malik@gmail.com>
To: vignesh C <vignesh21@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>, shveta malik <shveta.malik@gmail.com>
Date: 2025-09-29T04:28:49Z
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

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.

thanks
Shveta