Re: Logical Replication of sequences

On Tue, Aug 19, 2025 at 9:14 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> 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?

Not sure. The REFRESH command is specifically used to synchronize
values (such as last_value) of the local sequence to the remote ones,
but this only works when their definitions match. In contrast, DDL
replication for sequences handles changes to the sequence definition
itself. While DDLs are automatically replicated through logical
replication based on WAL records, the REFRESH command requires manual
execution by users. Therefore, I believe ALTER SEQUENCE statements
would be replicated regardless of when users execute the REFRESH
command. This means users would need to carefully consider the
ordering of these operations to prevent potential conflicts.

> 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.

Yes, I understand that conflicts can occur when users manually modify
sequence values or parameters on the subscriber. However, in Vignesh's
example, users are only executing the REFRESH command, without
performing any ALTER SEQUENCE commands or setval() operations on the
subscriber. In this scenario, I don't see why conflicts would arise
even with DDL replication in place.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com