Re: Logical Replication of sequences

On Wed, Aug 20, 2025 at 11:00 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Tue, Aug 19, 2025 at 9:14 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > 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.
>

This is because DDL can also fail if the existing sequence data does
not adhere to the DDL change. This will be true even for tables, but
let's focus on the sequence case. See below part of the example:

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

Here the user has intentionally reduced the existing value of the
sequence to (11) on the publisher after the REFRESH command and then
performed a DDL that is compatible with the latest RESTART value (11).
Now, because we did REFRESH before the user set the value of sequence
as 11, the current value on the subscriber will be 14. When we
replicate the DDL, it will find the latest RESTART value as (14)
greater than DDL's changed MAXVALUE (12), so it will fail, and the
subscriber will retry. Users have to manually perform REFRESH once
again, or maybe as part of a conflict resolution strategy, we can do
this internally. IIUC, we can't avoid this even if we start writing
WAL for the REFRESH command on the publisher.

--
With Regards,
Amit Kapila.