Re: Logical Replication of sequences

Amit Kapila <amit.kapila16@gmail.com>

From: Amit Kapila <amit.kapila16@gmail.com>

To: Peter Smith <smithpb2250@gmail.com>

Cc: vignesh C <vignesh21@gmail.com>, Shlok Kyal <shlok.kyal.oss@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-08T03:55:04Z

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

On Wed, Aug 7, 2024 at 10:12 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> This is mostly a repeat of my previous mail from a while ago [1] but
> includes some corrections, answers, and more examples. I'm going to
> try to persuade one last time because the current patch is becoming
> stable, so I wanted to revisit this syntax proposal before it gets too
> late to change anything.
>
> If there is some problem with the proposed idea please let me know
> because I can see only the advantages and no disadvantages of doing it
> this way.
>
> ~~~
>
> The current patchset offers two forms of subscription refresh:
> 1. ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option
> [= value] [, ... ] ) ]
> 2. ALTER SUBSCRIPTION name REFRESH PUBLICATION SEQUENCES
>
> Since 'copy_data' is the only supported refresh_option, really it is more like:
> 1. ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( copy_data [=
> true|false] ) ]
> 2. ALTER SUBSCRIPTION name REFRESH PUBLICATION SEQUENCES
>
> ~~~
>
> I proposed previously that instead of having 2 commands for refreshing
> subscriptions we should have a single refresh command:
>
> ALTER SUBSCRIPTION name REFRESH PUBLICATION [TABLES|SEQUENCES] [ WITH
> ( copy_data [= true|false] ) ]
>
> Why?
>
> - IMO it is less confusing than having 2 commands that both refresh
> sequences in slightly different ways
>
> - It is more flexible because apart from refreshing everything, a user
> can choose to refresh only tables or only sequences if desired; IMO
> more flexibility is always good.
>
> - There is no loss of functionality from the current implementation
> AFAICT. You can still say "ALTER SUBSCRIPTION sub REFRESH PUBLICATION
> SEQUENCES" exactly the same as the patchset allows.
>
> ~~~
>
> So, to continue this proposal, let the meaning of 'copy_data' for
> SEQUENCES be as follows:
>
> - when copy_data == false: it means don't copy data (i.e. don't
> synchronize anything). Add/remove sequences from pg_subscriber_rel as
> needed.
>
> - when copy_data == true: it means to copy data (i.e. synchronize) for
> all sequences. Add/remove sequences from pg_subscriber_rel as needed)
>

I find overloading the copy_data option more confusing than adding a
new variant for REFRESH. To make it clear, we can even think of
extending the command as ALTER SUBSCRIPTION name REFRESH PUBLICATION
ALL SEQUENCES or something like that.  I don't know where there is a
need or not but one can imagine extending it as ALTER SUBSCRIPTION
name REFRESH PUBLICATION SEQUENCES [<seq_name_1>, <seq_name_2>, ..].
This will allow to selectively refresh the sequences.

-- 
With Regards,
Amit Kapila.