Horton

Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: Peter Smith <smithpb2250@gmail.com>
Cc: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Masahiko Sawada <sawada.mshk@gmail.com>, shveta malik <shveta.malik@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Amit Kapila <amit.kapila16@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-04-28T12:56:56Z
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

Attachments

On Sat, 26 Apr 2025 at 14:24, Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Vignesh.
>
> Some review comments for v20250426-0005.
>
> 4b.
> AFAIK, when copy_data=false, then not only will *existing* sequences
> not be synchronised, but even the *new* sequences will not be
> synchronised. Effectively, when copy_data = false, then nothing at all
> happens for sequences as far as what the user sees, right?
>
> Experiment:
>
> test_pub=# create publication pub1 for all sequences;
> CREATE PUBLICATION
>
> test_sub=# create sequence s1;
> CREATE SEQUENCE
> NOTICE:  created replication slot "sub1" on publisher
> CREATE SUBSCRIPTION
>
> test_pub=# create sequence s1;
> CREATE SEQUENCE
> test_pub=# select * from nextval('s1');
>  nextval
> ---------
>        1
> (1 row)
>
> test_pub=# select * from nextval('s1');
>  nextval
> ---------
>        2
> (1 row)
>
> test_pub=# select * from nextval('s1');
>  nextval
> ---------
>        3
> (1 row)
>
> test_sub=# alter subscription sub1 refresh publication with (copy_data=false);
> ALTER SUBSCRIPTION
>
> test_sub=# select * from s1;
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>           1 |       0 | f
> (1 row)
>
> So, subscriber side s1 is unaffected.
>
> Maybe it is not worth the effort, but doesn't this mean that you could
> optimise the AlterSubscription_refresh() logic to completely skip all
> processing for sequences when copy_data=false. e.g. what's the point
> of gathering publisher sequence lists and setting INIT states for
> them, etc, when it won't synchronise anything because copy_data=false?
> Everything will be synchronised later anyway when the user does
> REFRESH PUBLICATION SEQUENCES.

Currently this is in line with table behavior, I felt let's keep it
that way so that it will be easier to extend sequence replication as
use cases grow and will become more in sync with tables.

The rest of the comments were fixed, the attached v20250428 version
patch has the changes for the same.
Also the issue reported at [1] is available in the attached patch.
[1] - https://www.postgresql.org/message-id/CAHut%2BPujY8Xd%3DT94zuPuF21s0dLRGVJaXgRnLbGE47pwSpo-YA%40mail.gmail.com

Regards,
Vignesh