Re: Logical Replication of sequences

On Tue, 19 Aug 2025 at 06:47, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Mon, Aug 18, 2025 at 4:21 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > For example, if a sequence is dropped
> > on the publisher, the subscriber would encounter synchronization
> > failures unless the DROP SEQUENCE is properly applied.
>
> This example is wrong. It seems DROP SEQUENCE works but we might have
> problems with ALTER SEQUENCE.

I also felt that DROP SEQUENCE does not pose a problem.

When it comes to ALTER SEQUENCE, there are two distinct cases to consider:
Case 1: Parameter Mismatch During REFRESH PUBLICATION SEQUENCES
Example:
-- Publisher
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;

-- Subscriber
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;

-- Publisher
ALTER SEQUENCE s1 MAXVALUE 12;

-- Subscriber
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;

In this scenario, the refresh fails with an error because the sequence
parameters no longer match:
2025-08-19 12:41:52.289 IST [209043] ERROR: logical replication
sequence synchronization failed for subscription "sub1"
2025-08-19 12:41:52.289 IST [209043] DETAIL: Mismatched sequence(s)
on subscriber: ("public.s1").
2025-08-19 12:41:52.289 IST [209043] HINT: For mismatched sequences,
alter or re-create local sequences to have matching parameters as
publishers.

In this case, the user simply needs to update the subscriber sequence
definition so that its parameters match the publisher.

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?

Regards,
Vignesh