Re: Logical Replication of sequences

On Mon, Aug 18, 2025 at 2:13 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> 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?

Thank you for the explanation.

IIUC even with DDL replication support for sequences, users would
still need to manage the order of DDL operations for sequences and
their synchronization (specifically when executing the REFRESH
PUBLICATION [SEQUENCE] command). For example, if a sequence is dropped
on the publisher, the subscriber would encounter synchronization
failures unless the DROP SEQUENCE is properly applied. This potential
issue concerns me.

I recall that Amit initially proposed an approach involving a special
NOOP record to enable the walsender to read and transmit sequence data
to the subscriber[1]. Have you considered incorporating this concept
into the current implementation? Under this approach, REFRESH
PUBLICATION [SEQUENCE] would simply trigger the subscriber to write a
special WAL record for sequence synchronization. Subsequently, when
decoding the WAL record, the walsender would collect sequence data
associated with its publications and transmit it to the subscriber.
The apply worker would then process sequence changes in the same
manner as table changes.

We could potentially optimize this process by including the LSN of the
last sequence synchronization in the WAL record, allowing the
walsender to transmit only those sequences whose page LSN exceeds this
value.

This thread is quite long so I may have missed some previous
discussion of these points, so I apologize if these matters have
already been addressed.

Regards,

[1] https://www.postgresql.org/message-id/CAA4eK1LC%2BKJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ%40mail.gmail.com

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