Re: Logical Replication of sequences

On Thu, 9 Oct 2025 at 12:30, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Thu, Oct 9, 2025 at 5:32 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Thu, Oct 9, 2025 at 11:32 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Oct 9, 2025 at 11:27 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > On Thu, Oct 9, 2025 at 10:14 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Wed, Oct 8, 2025 at 9:13 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > On Tue, Oct 7, 2025 at 5:46 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > > > >
> > > > > >
> > > > > > > > I have one more question: while testing the sequence sync, I found
> > > > > > > > this behavior is documented as well[1], but what's the reasoning
> > > > > > > > behind it? Why REFRESH PUBLICATION will synchronize only newly added
> > > > > > > > sequences and need to use REFRESH PUBLICATION SEQUENCES to
> > > > > > > > re-synchronize all sequences.
> > > > > > > >
> > > > > > >
> > > > > > > The idea is that REFRESH PUBLICATION should behave similarly for
> > > > > > > tables and sequences. This means that this command is primarily used
> > > > > > > to add/remove tables/sequences and copy their respective initial
> > > > > > > contents. The new command REFRESH PUBLICATION SEQUENCES is to sync the
> > > > > > > existing sequences, it shouldn't add any new sequences, now, if it is
> > > > > > > too confusing we can discuss having a different syntax for it.
> > > > > >
> > > > > > Sure, let's discuss this when we get this patch at the start of the
> > > > > > commit queue.
> > > > > >
> > > > >
> > > > > I have pushed the publications related patch. Now, we can discuss this
> > > > > command. I think confusion arises from the fact that both commands use
> > > > > REFRESH.
> > > >
> > > > Right
> > > >
> > > > So, how about for the second case (sync/copy all existing
> > > > > sequences), we use a different command, some ideas that come to my
> > > > > mind are:
> > > > >
> > > > > Alter Subscription sub1 REPLICATE Publication Sequences;
> > > > > Alter Subscription sub1 RESYNC Publication Sequences;
> > > > > Alter Subscription sub1 SYNC Publication Sequences;
> > > > > Alter Subscription sub1 MERGE Publication Sequences;
> > > > >
> > > > > Among these, the first three require a new keyword to be introduced. I
> > > > > prefer to use existing keyword if possible. Any ideas?
> > > >
> > > > I would have preferred "Alter Subscription sub1 SYNC Publication
> > > > Sequences" but if your preference is to use existing keywords then
> > > > IMHO "MERGE Publication Sequences" or "UPDATE Publication Sequences"
> > > > are also good options.
> > > >
> > >
> > > I would prefer "COPY Publication Sequences" or "UPDATE Publication
> > > Sequences" among the given options. We have a precedence for copy
> > > (copy_data) in publication command parameters, so, COPY could be a
> > > better option.
> > >
> >
> > If not SYNC, then COPY looks the next best option to me.
> >
>
> Something about all these ideas seems strange to me:
>
> I think the "ALTER SUBSCRIPTION sub REFRESH PUBLICATION" command has
> the word PUBLICATION in it because it's the PUBLICATION has changed
> (stuff added/removed), so we need to refresh it.
>
> OTOH, the synchronisation of *existing* sequences is different - this
> is more like the subscription saying "Just get me updated values for
> the sequences I already know about". Therefore, I don't think the word
> PUBLICATION is relevant here.
>
> ~~
>
> So my suggestion is very different. Just this:
> "ALTER SUBSCRIPTION sub REFRESH SEQUENCES"
>
> I feel this is entirely consistent, because:
>
> PUBLICATION objects have changed. Refresh me the new objects => ALTER
> SUBSCRIPTION sub REFRESH PUBLICATION;
>
> SEQUENCE values have changed. Refresh me the new values => ALTER
> SUBSCRIPTION sub REFRESH SEQUENCES;

+1 for this syntax. Here is an updated patch having the changes for the same.

Regards,
Vignesh