Re: Logical Replication of sequences - Mailing list pgsql-hackers

On Fri, Jun 7, 2024 at 7:30 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Jun 7, 2024 at 7:55 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Thu, Jun 6, 2024 at 6:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > 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?
> >
> > 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.
> >
> > Copying sequence data works differently than replicating table data
> > (initial data copy and logical replication). So I thought the
> > copy_sequence option (or whatever better name) always does both
> > updating pre-existing sequences and adding new sequences. REFRESH
> > PUBLICATION updates the tables to be subscribed, so we also update or
> > add sequences associated to these tables.
> >
>
> 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. 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?

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? The WIP patch Vignesh recently submitted is more
than 6k lines. I think we can cut the scope for the first
implementation so as to make the review easy.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com