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-19T08:43:52Z
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 Tue, 19 Aug 2025 at 06:47, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Mon, Aug 18, 2025 at 4:21 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > For example, if a sequence is dropped
> > on the publisher, the subscriber would encounter synchronization
> > failures unless the DROP SEQUENCE is properly applied.
>
> This example is wrong. It seems DROP SEQUENCE works but we might have
> problems with ALTER SEQUENCE.

I also felt that DROP SEQUENCE does not pose a problem.

When it comes to ALTER SEQUENCE, there are two distinct cases to consider:
Case 1: Parameter Mismatch During REFRESH PUBLICATION SEQUENCES
Example:
-- Publisher
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;

-- Subscriber
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;

-- Publisher
ALTER SEQUENCE s1 MAXVALUE 12;

-- Subscriber
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;

In this scenario, the refresh fails with an error because the sequence
parameters no longer match:
2025-08-19 12:41:52.289 IST [209043] ERROR:  logical replication
sequence synchronization failed for subscription "sub1"
2025-08-19 12:41:52.289 IST [209043] DETAIL:  Mismatched sequence(s)
on subscriber: ("public.s1").
2025-08-19 12:41:52.289 IST [209043] HINT:  For mismatched sequences,
alter or re-create local sequences to have matching parameters as
publishers.

In this case, the user simply needs to update the subscriber sequence
definition so that its parameters match the publisher.

Case 2: Sequence value Conflict While Applying DDL Changes(Future patch)

Example:
-- Publisher
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;
SELECT nextval('s1'); -- called several times, advancing sequence to 14

-- Subscriber
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
SELECT currval('s1');
 currval
---------
      14

Now on the publisher:
SELECT setval('s1', 11);
ALTER SEQUENCE s1 MAXVALUE 12;

When applying the DDL change on the subscriber:
ERROR:  RESTART value (14) cannot be greater than MAXVALUE (12)

This illustrates a value conflict between the current state of the
sequence on the subscriber and the altered definition from the
publisher.

For such cases, we could consider:
Allowing the user to resolve the conflict manually, or
Providing an option to reset the sequence automatically.

A similar scenario can also occur with tables if a DML operation is
executed on the subscriber.

I’m still not entirely sure which of these scenarios you were referring to.
Were you pointing to Case 2 (value conflict), or do you have another
case in mind?

Regards,
Vignesh