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: "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>, Masahiko Sawada <sawada.mshk@gmail.com>, Shlok Kyal <shlok.kyal.oss@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-07-30T05:46:14Z
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 Mon, Jul 28, 2025 at 3:37 PM vignesh C <vignesh21@gmail.com> wrote:

> Thanks for the comments, the attached v20250728 version patch has the
> changes for the same.
>
Thanks for the patches, please find a few comments:

1)
WARNING:  WITH clause parameters do not affect sequence synchronization

a)
How about:
WITH clause parameters are not applicable to sequence synchronization
or
WITH clause parameters are not applicable to sequence synchronization
and will be ignored.

b)
Should it be NOTICE OR WARNING? I feel NOTICE Is more appropriate as
it is more of an information than a warning since it has no negative
consequences.

2)
 AlterSubscription_refresh(Subscription *sub, bool copy_data,
-   List *validate_publications)
+   List *validate_publications, bool refresh_tables,
+   bool refresh_sequences, bool resync_all_sequences)
 {

Do we need 3 new arguments? If we notice, 'refresh_sequences' is
always true in all cases. I feel only the last one should suffice.
IIUC, this is the state:

When resync_all_sequences is true:
it indicates it is 'REFRESH PUBLICATION SEQUENCES', that means we have
to refresh new sequences and resync all sequences.

When resync_all_sequences is false:
That means it is 'REFRESH PUBLICATION', we have to refresh new tables
and new sequences alone.

So if the caller pass only 'resync_all_sequences', we should be able
to drive the rest of the values internally.

3)
 ALTER SUBSCRIPTION regress_testsub REFRESH PUBLICATION;
-ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot run inside a transaction block
+ERROR:  ALTER SUBSCRIPTION ... REFRESH PUBLICATION cannot run inside
a transaction block

In the same script, we can test REFRESH PUBLICATION SEQUENCES also in
trancsation block.

4)
Commit message of patch004 says:

This patch introduce a new command to synchronize the sequences of
a subscription:
ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES

a)
introduce --> introduces

b)
We should also add:

This patch also changes the scope of
ALTER SUBSCRIPTION ... REFRESH PUBLICATION
This command now also considers sequences (newly added or dropped ones).

5)
+ * Reset the last_start_time of the sequencesync worker in the subscription's
+ * apply worker.

last_start_time-->last_seqsync_start_time

6)
alter_subscription.sgml has this:
        <term><literal>refresh</literal> (<type>boolean</type>)</term>
        <listitem>
         <para>
          When false, the command will not try to refresh table information.
          <literal>REFRESH PUBLICATION</literal> should then be
executed separately.
          The default is <literal>true</literal>.
         </para>
        </listitem>
       </varlistentry>

Shouldn't we mention sequence too here:
When false, the command will not try to refresh table and sequence information.

thanks
Shveta