Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

From: vignesh C <vignesh21@gmail.com>

To: shveta malik <shveta.malik@gmail.com>

Cc: Peter Smith <smithpb2250@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Hou, Zhijie/侯志杰 <houzj.fnst@fujitsu.com>, "Jonathan S. Katz" <jkatz@postgresql.org>

Date: 2024-07-29T10:47:35Z

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 →

Doc: Add documentation for sequence synchronization.
- 55cefadde874 19 (unreleased) landed
Remove unused assignment in CREATE PUBLICATION grammar.
- bfb7419b0bbe 19 (unreleased) landed
Add seq_sync_error_count to subscription statistics.
- f6a4c498dcf6 19 (unreleased) landed
Fix few issues in commit 5509055d69.
- 5a4eba558aa7 19 (unreleased) landed
Add sequence synchronization for logical replication.
- 5509055d6956 19 (unreleased) landed
Add worker type argument to logical replication worker functions.
- 3e8e05596a02 19 (unreleased) landed
Introduce "REFRESH SEQUENCES" for subscriptions.
- f0b3573c3aac 19 (unreleased) landed
Refactor logical worker synchronization code into a separate file.
- 41c674d2e31e 19 (unreleased) landed
Standardize use of REFRESH PUBLICATION in code and messages.
- 2436b8c047ff 19 (unreleased) landed
Add "ALL SEQUENCES" support to publications.
- 96b378497346 19 (unreleased) landed
Expose sequence page LSN via pg_get_sequence_data.
- b93172ca59f4 19 (unreleased) landed
Resume conflict-relevant data retention automatically.
- 0d48d393d465 19 (unreleased) cited
Post-commit review fixes for 228c370868.
- 1f7e9ba3ac4e 19 (unreleased) cited
Generate GUC tables from .dat file
- 63599896545c 19 (unreleased) cited

Attachments

v20240730_2-0001-Introduce-pg_sequence_state-function-for.patch (text/x-patch) patch v20240730-0001
v20240730_2-0002-Introduce-ALL-SEQUENCES-support-for-Post.patch (text/x-patch) patch v20240730-0002
v20240730_2-0003-Enhance-sequence-synchronization-during-.patch (text/x-patch) patch v20240730-0003

On Thu, 25 Jul 2024 at 12:08, shveta malik <shveta.malik@gmail.com> wrote:
>
> On Thu, Jul 25, 2024 at 9:06 AM vignesh C <vignesh21@gmail.com> wrote:
> >
> > The attached v20240725 version patch has the changes for the same.
>
> Thank You for addressing the comments. Please review below issues:
>
> 1) Sub ahead of pub due to wrong initial sync of last_value for
> non-incremented sequences. Steps at [1]
> 2) Sequence's min value is not honored on sub during replication. Steps at [2]
>
> [1]:
> -----------
> on PUB:
> CREATE SEQUENCE myseq001 INCREMENT 5 START 100;
> SELECT * from pg_sequences; -->shows last_val as NULL
>
> on SUB:
> CREATE SEQUENCE myseq001 INCREMENT 5 START 100;
> SELECT * from pg_sequences; -->correctly shows last_val as NULL
> ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
> SELECT * from pg_sequences;  -->wrongly updates last_val to 100; it is
> still NULL on Pub.
>
> Thus , SELECT nextval('myseq001') on pub gives 100, while on sub gives 105.
> -----------
>
>
> [2]:
> -----------
> Pub:
> CREATE SEQUENCE myseq0 INCREMENT 5 START 10;
> SELECT * from pg_sequences;
>
> Sub:
> CREATE SEQUENCE myseq0 INCREMENT 5 MINVALUE 100;
>
> Pub:
> SELECT nextval('myseq0');
> SELECT nextval('myseq0');
>
> Sub:
> ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
> --check 'last_value', it is 15 while min_value is 100
> SELECT * from pg_sequences;

Thanks for reporting this, these issues are fixed in the attached
v20240730_2 version patch.

Regards,
Vignesh