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

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

>
> >
> > > >
> > > > 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.
>
> It seems to make sense to me. But I have one question: do we want to
> support replicating sequences that are not associated with any tables?
>

Yes, unless we see a problem with it.

> if yes, what if we refresh two different subscriptions that subscribe
> to different tables on the same database?

What problem do you see with it?

>
 On the other hand, if no
> (i.e. replicating only sequences owned by tables), can we know which
> sequences to replicate by checking the subscribed tables?
>

Sorry, I didn't understand your question. Can you please try to
explain in more words or use some examples?

--
With Regards,
Amit Kapila.