Horton

Re: Logical Replication of sequences

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>, shveta malik <shveta.malik@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Nisha Moond <nisha.moond412@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Peter Smith <smithpb2250@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, "Jonathan S. Katz" <jkatz@postgresql.org>
Date: 2025-08-21T04:04:40Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Add documentation for sequence synchronization.

  2. Remove unused assignment in CREATE PUBLICATION grammar.

  3. Add seq_sync_error_count to subscription statistics.

  4. Fix few issues in commit 5509055d69.

  5. Add sequence synchronization for logical replication.

  6. Add worker type argument to logical replication worker functions.

  7. Introduce "REFRESH SEQUENCES" for subscriptions.

  8. Refactor logical worker synchronization code into a separate file.

  9. Standardize use of REFRESH PUBLICATION in code and messages.

  10. Add "ALL SEQUENCES" support to publications.

  11. Expose sequence page LSN via pg_get_sequence_data.

  12. Resume conflict-relevant data retention automatically.

  13. Post-commit review fixes for 228c370868.

  14. Generate GUC tables from .dat file

On Wed, Aug 20, 2025 at 11:00 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, Aug 19, 2025 at 9:14 PM Amit Kapila <amit.kapila16@gmail.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.

-- 
With Regards,
Amit Kapila.