Re: Logical Replication of sequences

On Tue, Aug 19, 2025 at 1:44 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> 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?

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.

Regards,

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