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: 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-13T12:03:42Z
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 Tue, 13 Aug 2024 at 12:31, Peter Smith <smithpb2250@gmail.com> wrote:
>
> 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.

I have changed it to debug1 log level how we do for tables, so this
will not appear for  default log level

>
> 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?

I felt this is ok, no need to order it as it can easily be done using
some scripts if required from logs.

The rest of the issues were fixed, the v20240813 version patch
attached at [1] has the changes for the same.
[1] - https://www.postgresql.org/message-id/CALDaNm1Nr_n9SBB52L8A10Txyb4nqGJWfHUapwzM5BopvjMhjA%40mail.gmail.com

Regards,
Vignesh