Horton

Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Hou, Zhijie/侯 志杰 <houzj.fnst@fujitsu.com>, "Jonathan S. Katz" <jkatz@postgresql.org>
Date: 2024-06-19T15:03:37Z
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 Tue, 18 Jun 2024 at 16:10, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
>
> Agreed and I am not sure which is better because there is a value in
> keeping the state name the same for both sequences and tables. We
> probably need more comments in code and doc updates to make the
> behavior clear. We can start with the sequence state as 'init' for
> 'needs-to-be-sycned' and 'ready' for 'synced' and can change if others
> feel so during the review.

Here is a patch which does the sequence synchronization in the
following lines from the above discussion:
This commit introduces sequence synchronization during 1) creation of
subscription for initial sync of sequences 2) refresh publication to
synchronize the sequences for the newly created sequences 3) refresh
publication sequences for synchronizing all the sequences.
1) During subscription creation with CREATE SUBSCRIPTION (no syntax change):
   - The subscriber retrieves sequences associated with publications.
   - Sequences  are added in the 'init' state to the pg_subscription_rel table.
   - Sequence synchronization worker will be started if there are any
sequences to be synchronized
   - A new sequence synchronization worker handles synchronization in
batches of 100 sequences:
     a) Retrieves sequence values using pg_sequence_state from the publisher.
     b) Sets sequence values accordingly.
     c) Updates sequence state to 'READY' in pg_susbcripion_rel
     d) Commits batches of 100 synchronized sequences.
2) Refreshing sequences with ALTER SUBSCRIPTION ... REFRESH
PUBLICATION (no syntax change):
   - Stale sequences are removed from pg_subscription_rel.
   - Newly added sequences in the publisher are added in 'init' state
to pg_subscription_rel.
   - Sequence synchronization will be done by sequence sync worker as
listed in subscription creation process.
   - Sequence synchronization occurs for newly added sequences only.
3) Introduce new command ALTER SUBSCRIPTION ... REFRESH PUBLICATION
SEQUENCES  for refreshing all sequences:
   - Removes stale sequences and adds newly added sequences from the
publisher to pg_subscription_rel.
   - Resets all sequences in pg_subscription_rel to 'init' state.
   - Initiates sequence synchronization for all sequences by sequence
sync worker as listed in subscription creation process.

Regards,
Vignesh