Re: Logical Replication of sequences

Peter Smith <smithpb2250@gmail.com>

From: Peter Smith <smithpb2250@gmail.com>

To: vignesh C <vignesh21@gmail.com>

Cc: shveta malik <shveta.malik@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-08-13T07:00:53Z

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,

I have been using the latest patchset, trying a few things using many
(1000) sequences.

Here are some observations, plus some suggestions for consideration.

~~~~~

OBSERVATION #1

When 1000s of sequences are refreshed using REFRESH PUBLICATION
SEQUENCES the logging is excessive. For example, since there is only
one sequencesync worker why does it need to broadcast that it is
"finished" separately for every single sequence. That is giving 1000s
of lines of logs which don't seem to be of much interest to a user.

...
2024-08-13 16:17:04.151 AEST [5002] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0918" has
finished
2024-08-13 16:17:04.151 AEST [5002] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0919" has
finished
2024-08-13 16:17:04.151 AEST [5002] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0920" has
finished
2024-08-13 16:17:04.151 AEST [5002] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0921" has
finished
2024-08-13 16:17:04.151 AEST [5002] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0922" has
finished
2024-08-13 16:17:04.151 AEST [5002] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0923" has
finished
...

Perhaps just LOG when each "batch" is completed, but the individual
sequence finished logs can just be DEBUG information?

~~~~~

OBSERVATION #2

When 1000s of sequences are refreshed (set to INIT) then there are
1000s of logs like below:

...
2024-08-13 16:13:57.873 AEST [10301] LOG:  sequence "public.seq_0698"
of subscription "sub3" set to INIT state
2024-08-13 16:13:57.873 AEST [10301] STATEMENT:  alter subscription
sub3 refresh publication sequences;
2024-08-13 16:13:57.873 AEST [10301] LOG:  sequence "public.seq_0699"
of subscription "sub3" set to INIT state
2024-08-13 16:13:57.873 AEST [10301] STATEMENT:  alter subscription
sub3 refresh publication sequences;
2024-08-13 16:13:57.873 AEST [10301] LOG:  sequence "public.seq_0700"
of subscription "sub3" set to INIT state
2024-08-13 16:13:57.873 AEST [10301] STATEMENT:  alter subscription
sub3 refresh publication sequences;
2024-08-13 16:13:57.873 AEST [10301] LOG:  sequence "public.seq_0701"
of subscription "sub3" set to INIT state
2024-08-13 16:13:57.873 AEST [10301] STATEMENT:  alter subscription
sub3 refresh publication sequences;
2024-08-13 16:13:57.874 AEST [10301] LOG:  sequence "public.seq_0702"
of subscription "sub3" set to INIT state
2024-08-13 16:13:57.874 AEST [10301] STATEMENT:  alter subscription
sub3 refresh publication sequences;
...

I felt that showing the STATEMENT for all of these is overkill. How
about changing that ereport LOG so it does not emit the statement 1000
times? Or, maybe you can implement it as a "dynamic" log that emits
the STATEMENT if there are only a few logs a few times but skips it
for the next 995 logs.

~~~~~

OBSERVATION #3

The WARNING about mismatched sequences currently looks like this:

2024-08-13 16:41:45.496 AEST [10301] WARNING:  Parameters differ for
remote and local sequences "public.seq_0999"
2024-08-13 16:41:45.496 AEST [10301] HINT:  Alter/Re-create the
sequence using the same parameter as in remote.

Although you could probably deduce it from nearby logs, I think it
might be more helpful to also identify the subscription name in this
WARNING message. Otherwise, if there are many publications the user
may have no idea where the mismatched "remote" is coming from.

~~~~

OBSERVATION #4

When 1000s of sequences are refreshed then there are 1000s of
associated logs. But (given there is only one sequencesync worker)
those logs are not always the order that I was expecting to see them.

e.g.
...
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0885" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0887" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0888" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0889" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0890" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0906" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0566" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0568" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0569" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0570" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0571" has
finished
2024-08-13 16:41:47.436 AEST [11735] LOG:  logical replication
synchronization for subscription "sub3", sequence "seq_0582" has
finished
...

Is there a way to refresh sequences in a more natural (e.g.
alphabetical) order to make these logs more readable?

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