Re: Logical Replication of sequences

On Tue, Aug 19, 2025 at 11:33 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Tue, Aug 19, 2025 at 1:44 AM vignesh C <vignesh21(at)gmail(dot)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.