Re: Logical Replication of sequences

Amit Kapila <amit.kapila16@gmail.com>

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

To: Masahiko Sawada <sawada.mshk@gmail.com>

Cc: 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>, "Katz, Jonathan" <jkatz@amazon.com>

Date: 2024-06-10T09:18:25Z

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 Mon, Jun 10, 2024 at 12:43 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Mon, Jun 10, 2024 at 3:14 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Fri, Jun 7, 2024 at 7:30 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > >
> > > Are you imagining the behavior for sequences associated with tables
> > > differently than the ones defined by the CREATE SEQUENCE .. command? I
> > > was thinking that users would associate sequences with publications
> > > similar to what we do for tables for both cases. For example, they
> > > need to explicitly mention the sequences they want to replicate by
> > > commands like CREATE PUBLICATION ... FOR SEQUENCE s1, s2, ...; CREATE
> > > PUBLICATION ... FOR ALL SEQUENCES, or CREATE PUBLICATION ... FOR
> > > SEQUENCES IN SCHEMA sch1;
> > >
> > > In this, variants FOR ALL SEQUENCES and SEQUENCES IN SCHEMA sch1
> > > should copy both the explicitly defined sequences and sequences
> > > defined with the tables. Do you think a different variant for just
> > > copying sequences implicitly associated with tables (say for identity
> > > columns)?
> >
> > Oh, I was thinking that your proposal was to copy literally all
> > sequences by REPLICA/REFRESH SEQUENCE command.
> >

I am trying to keep the behavior as close to tables as possible.

> > But it seems to make
> > sense to explicitly specify the sequences they want to replicate. It
> > also means that they can create a publication that has only sequences.
> > In this case, even if they create a subscription for that publication,
> > we don't launch any apply workers for that subscription. Right?
> >

Right, good point. I had not thought about this.

> > Also, given that the main use case (at least as the first step) is
> > version upgrade, do we really need to support SEQUENCES IN SCHEMA and
> > even FOR SEQUENCE?
>

At the very least, we can split the patch to move these variants to a
separate patch. Once the main patch is finalized, we can try to
evaluate the remaining separately.

> Also, I guess that specifying individual sequences might not be easy
> to use for users in some cases. For sequences owned by a column of a
> table, users might want to specify them altogether, rather than
> separately. For example, CREATE PUBLICATION ... FOR TABLE tab1 WITH
> SEQUENCES means to add the table tab1 and its sequences to the
> publication. For other sequences (i.e., not owned by any tables),
> users might want to specify them individually.
>

Yeah, or we can have a syntax like CREATE PUBLICATION ... FOR TABLE
tab1 INCLUDE SEQUENCES.  Normally, we use the WITH clause for options
(For example, CREATE SUBSCRIPTION ... WITH (streaming=...)).

-- 
With Regards,
Amit Kapila.