Re: Logical Replication of sequences

On Tue, Oct 7, 2025 at 5:46 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Oct 7, 2025 at 4:56 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Tue, Oct 7, 2025 at 3:51 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > On Tue, Oct 7, 2025 at 2:21 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > On Mon, Oct 6, 2025 at 4:33 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Mon, 6 Oct 2025 at 12:07, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > On Sat, 4 Oct 2025 at 21:24, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > > > >
> > > > > > > On Tue, Sep 30, 2025 at 9:55 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > > > > > >
> > > > > > >
> > > > > > > In the 0001 patch, pg_get_sequence_data() exposes two new fields
> > > > > > > log_cnt and page_lsn. I see that the later subscriber-side patch uses
> > > > > > > both, the first one in SetSequence(). It is not clear from the
> > > > > > > comments or the commit message of 0001 why it is necessary to use
> > > > > > > log_cnt when setting the sequence. Can you explain what the problem
> > > > > > > will be if we don't use log_cnt during sequence sync?
> > > > > >
> > > > > > I thought to keep the log_cnt value the same value as the publisher.
> > > > > > I have verified from the upgrade that we don't retain the log_cnt
> > > > > > value after upgrade, even if we copy log_cnt, the value will not be
> > > > > > retained. The attached
> > > > > > v20251006-0001-Enhance-pg_get_sequence_data-function.patch has the
> > > > > > changes to remove log_cnt.
> > > > >
> > > > > Here is the rebased remaining patches.
> > > >
> > > > While testing the patches with different combinations to make
> > > > publications, I do not understand why we don't support ALL SEQUENCE
> > > > with some table option, or is it future pending work.
> > > >
> > >
> > > Yes, it is left for future similar to the cases like FOR SEQUENCE s1
> > > or FOR SEQUENCES IN SCHEMA. The key idea was to first support the
> > > cases required for upgrade and we can later extend the feature after
> > > some user feedback or separate discussion with -hackers to see what
> > > others think. Does that sound reasonable to you?
> >
> > Yeah that's correct, I think the main use case for sequence
> > synchronization is upgrade and it makes sense to use ALL TABLES/ALL
> > SEQUENCES for upgrade. However, if a user is using selective tables
> > for upgrade for now they might not be able to use ALL SEQUENCE and
> > that should be fine as we are going to provide add on functionality.
> >
>
> Yes, we can add the additional functionality for selective sequences
> if required but do we have an option to allow upgrade of selective
> tables?

If the user is upgrading using logical replication, then there is an
option to set up a replication from the current version to the next
major version and then the user can selectively publish the table
which is supposed to be streamed to the next major version. right?

> > 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.

In the
> meantime, let's make the 0001 publication-side patch ready for commit.

Make sense.

--
Regards,
Dilip Kumar
Google