Horton

Re: Logical Replication of sequences

Amit Kapila <amit.kapila16@gmail.com>

View thread
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-06T09:40:06Z
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 Thu, Jun 6, 2024 at 11:10 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jun 5, 2024 at 9:30 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
>
> > To achieve this, we can allow sequences to be copied during
> > the initial CREATE SUBSCRIPTION command similar to what we do for
> > tables. And then later by new/existing command, we re-copy the already
> > existing sequences on the subscriber.
> >
> > The options for the new command could be:
> > Alter Subscription ... Refresh Sequences
> > Alter Subscription ... Replicate Sequences
> >
> > In the second option, we need to introduce a new keyword Replicate.
> > Can you think of any better option?
>
> Another idea is doing that using options. For example,
>
> For initial sequences synchronization:
>
> CREATE SUBSCRIPTION ... WITH (copy_sequence = true);
>

How will it interact with the existing copy_data option? So copy_data
will become equivalent to copy_table_data, right?

> For re-copy (or update) sequences:
>
> ALTER SUBSCRIPTION ... REFRESH PUBLICATION WITH (copy_sequence = true);
>

Similar to the previous point it can be slightly confusing w.r.t
copy_data. And would copy_sequence here mean that it would copy
sequence values of both pre-existing and newly added sequences, if so,
that would make it behave differently than copy_data? The other
possibility in this direction would be to introduce an option like
replicate_all_sequences/copy_all_sequences which indicates a copy of
both pre-existing and new sequences, if any.

If we want to go in the direction of having an option such as
copy_(all)_sequences then do you think specifying that copy_data is
just for tables in the docs would be sufficient? I am afraid that it
would be confusing for users.

> >
> > In addition to the above, the command Alter Subscription .. Refresh
> > Publication will fetch any missing sequences similar to what it does
> > for tables.
>
> On the subscriber side, do we need to track which sequences are
> created via CREATE/ALTER SUBSCRIPTION?
>

I think so unless we find some other way to know at refresh
publication time which all new sequences need to be part of the
subscription. What should be the behavior w.r.t sequences when the
user performs ALTER SUBSCRIPTION ... REFRESH PUBLICATION? I was
thinking similar to tables, it should fetch any missing sequence
information from the publisher.

-- 
With Regards,
Amit Kapila.