Re: Logical Replication of sequences

On Wed, Aug 20, 2025 at 9:04 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> 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.

Right. Since DMLs and DDLs for sequences are replicated and applied to
the subscriber out of order even if we write WAL for the REFRESH
command.

On the other hand, there is a scenario where we can cover with the
idea of writing a WAL for the REFRESH command:

-- Publisher
CREATE s as integer;
select setval('s', pow(2,31)::int)

-- Subscriber
CREATE s as integer;
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
-- the last value of 's' is 1073741824

-- Publisher
alter sequence s as bigint;
select setval('s', pow(2,50)::bigint);

-- Subscriber
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
-- sequence synchronization keeps failing due to mismatch sequence
definition until ALTER SEQUENCE DDL is applied to the subscriber.

I'm not suggesting to change the current approach but I'd just like to
figure out how sequence replication will work with future DDL
replication if we implement sequence synchronization as a logical
replication feature.

Regards,

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