Horton

Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: shveta malik <shveta.malik@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>, 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>
Date: 2025-08-18T09:13:13Z
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 Sat, 16 Aug 2025 at 14:15, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> As I understand it, the logical replication of sequences implemented
> by these patches shares the same user interface as table replication
> (utilizing CREATE PUBLICATION and CREATE SUBSCRIPTION commands for
> configuration). However, the underlying replication mechanism totally
> differs from table replication. While table replication sends
> changesets extracted from WAL records (i.e., changes are applied in
> commit LSN order), sequence replication
> synchronizes the subscriber's sequences with the publisher's current
> state. This raises an interesting theoretical question: In a scenario
> where we implement DDL replication (extracting and replicating DDL
> statements from WAL records to subscribers, as previously proposed),
> how would sequence-related DDL replication interact with the sequence
> synchronization mechanism implemented in this patch?

The handling of sequence DDL should mirror how we manage table DDL:
1. During CREATE SUBSCRIPTION - Create sequences along with
tables—there’s no issue when initializing them during the initial
sync.
2. During Incremental Synchronization - Treat sequence changes like
table changes:
2.a Creating new sequences: Apply the creation on the subscriber side
when the corresponding WAL record appears.
2.b Dropping sequences: Handle drops in the same way they should
propagate and execute on the subscriber.
2.c. Handling Modifications to Existing Sequences
Sequence DDL changes can lead to two different outcomes:
i) No Conflict - If the change applies cleanly, accept and apply it immediately.
ii) Conflict
An example:
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;
SELECT nextval('s1') — called several times, advancing the sequence
ALTER SEQUENCE s1 MAXVALUE 12;
-- Error:
ERROR:  RESTART value (14) cannot be greater than MAXVALUE (12)

In such conflict cases, we should consider using setval() with
is_called = false to adjust the sequence safely and avoid errors.

Thoughts?

Regards,
Vignesh