Horton

Re: Logical Replication of sequences

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>, shveta malik <shveta.malik@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Nisha Moond <nisha.moond412@gmail.com>, Dilip Kumar <dilipbalaut@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-20T04:14:24Z
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, Aug 19, 2025 at 11:33 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, Aug 19, 2025 at 1:44 AM vignesh C <vignesh21@gmail.com> wrote:
> >
> >
> > 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?
>
> I imagined something like case 2. For logical replication of tables,
> if we support DDL replication (i.e., CREATE/ALTER/DROP TABLE), all
> changes the apply worker executes are serialized in commit LSN order.
> Therefore, users would not have to be concerned about schema changes
> that happened to the publisher. On the other hand, for sequence
> replication, even if we support DDL replication for sequences (i.e.,
> CREATE/ALTER/DROP SEQUENCES), users would have to execute REFRESH
> PUBLICATION SEQUENCES command after "ALTER SEQUENCE s1 MAXVALUE 12;"
> has been replicated on the subscriber. Otherwise, REFRESH PUBLICATION
> SEQUENCE command would fail because the sequence parameters no longer
> match.
>

In the example provided by Vignesh, it should do REFRESH before the
ALTER SEQUENCE command; otherwise, the ALTER SEQUENCE won't be
replicated, right? If so, I don't think we can do much with the design
choice we made. During DDL replication of sequences, we need to
consider it as a conflict.

BTW, note that the same situation can happen even when the user
manually changed the sequence value on the subscriber in some way. So,
we can't prevent that.

-- 
With Regards,
Amit Kapila.