Re: Logical Replication of sequences

On Sat, 16 Aug 2025 at 14:15, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> As I understand it, the logical replication of sequences implemented
> by these patches shares the same user interface as table replication
> (utilizing CREATE PUBLICATION and CREATE SUBSCRIPTION commands for
> configuration). However, the underlying replication mechanism totally
> differs from table replication. While table replication sends
> changesets extracted from WAL records (i.e., changes are applied in
> commit LSN order), sequence replication
> synchronizes the subscriber's sequences with the publisher's current
> state. This raises an interesting theoretical question: In a scenario
> where we implement DDL replication (extracting and replicating DDL
> statements from WAL records to subscribers, as previously proposed),
> how would sequence-related DDL replication interact with the sequence
> synchronization mechanism implemented in this patch?

The handling of sequence DDL should mirror how we manage table DDL:
1. During CREATE SUBSCRIPTION - Create sequences along with
tables—there’s no issue when initializing them during the initial
sync.
2. During Incremental Synchronization - Treat sequence changes like
table changes:
2.a Creating new sequences: Apply the creation on the subscriber side
when the corresponding WAL record appears.
2.b Dropping sequences: Handle drops in the same way they should
propagate and execute on the subscriber.
2.c. Handling Modifications to Existing Sequences
Sequence DDL changes can lead to two different outcomes:
i) No Conflict - If the change applies cleanly, accept and apply it immediately.
ii) Conflict
An example:
CREATE SEQUENCE s1 MINVALUE 10 MAXVALUE 20;
SELECT nextval('s1') — called several times, advancing the sequence
ALTER SEQUENCE s1 MAXVALUE 12;
-- Error:
ERROR: RESTART value (14) cannot be greater than MAXVALUE (12)

In such conflict cases, we should consider using setval() with
is_called = false to adjust the sequence safely and avoid errors.

Thoughts?

Regards,
Vignesh