Re: Logical Replication of sequences

Peter Smith <smithpb2250@gmail.com>

From: Peter Smith <smithpb2250@gmail.com>

To: vignesh C <vignesh21@gmail.com>

Cc: Chao Li <li.evan.chao@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, Dilip Kumar <dilipbalaut@gmail.com>, shveta malik <shveta.malik@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Jonathan S. Katz" <jkatz@postgresql.org>

Date: 2025-10-29T03:49:24Z

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

Hi Vignesh,

Some review comments patch V20251029-0001 (the test code only)

======
src/test/subscription/t/036_sequences.pl

1.
+##########
+## ALTER SUBSCRIPTION ... REFRESH PUBLICATION with (copy_data = off) should
+# not update the sequence values for the new sequence.
+##########
+
+# Create a new sequence 'regress_s4'
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE SEQUENCE regress_s4;
+ INSERT INTO regress_seq_test SELECT nextval('regress_s4') FROM
generate_series(1,100);
+));
+

AFAICT the sequence `regress_s3` (from the previous test part) was
already a "new sequence" that had not yet been REFRESHED to the
subscriber. So I think maybe there wasn't any need to create another
sequence `regress_s4` for this test part.

~~~

2.
+# Check - newly published sequence values are not updated
+$result = $node_subscriber->safe_psql(
+ 'postgres', qq(
+ SELECT last_value, log_cnt, is_called FROM regress_s4;
+));

Maybe that comment can give more details:
# Check - newly published sequence values are not updated when (copy_data = off)

~~~

3.
+##########
+# ALTER SUBSCRIPTION ... REFRESH PUBLICATION should report an error when:
+# a) sequence definitions differ between the publisher and subscriber, or
+# b) a sequence is missing on the publisher.
+##########

OK, you have these mismatch parameters and missing sequences test for
"REFRESH PUBLICATION", but what about doing the same tests for
"REFRESH SEQUENCES" -- e,g, I am thinking you can ALTER/DROP some
publication that previously had synchronized OK, to verify what
happens during "REFRESH SEQUENCES".

======
Kind Regards,
Peter Smith.
Fujitsu Australia