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

On Tue, Jun 11, 2024 at 12:25 PM vignesh C <vignesh21@gmail.com> wrote:
>
> On Mon, 10 Jun 2024 at 14:48, Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Mon, Jun 10, 2024 at 12:43 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > > On Mon, Jun 10, 2024 at 3:14 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > > >
> > > > On Fri, Jun 7, 2024 at 7:30 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > > >
> > > > >
> > > > > 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.
> > > >
> >
> > I am trying to keep the behavior as close to tables as possible.
> >
> > > > 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?
> > > >
> >
> > Right, good point. I had not thought about this.
> >
> > > > 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?
> > >
> >
> > At the very least, we can split the patch to move these variants to a
> > separate patch. Once the main patch is finalized, we can try to
> > evaluate the remaining separately.
>
> I engaged in an offline discussion with Amit about strategizing the
> division of patches to facilitate the review process. We agreed on the
> following split: The first patch will encompass the setting and
> getting of sequence values (core sequence changes). The second patch
> will cover all changes on the publisher side related to "FOR ALL
> SEQUENCES." The third patch will address subscriber side changes aimed
> at synchronizing "FOR ALL SEQUENCES" publications. The fourth patch
> will focus on supporting "FOR SEQUENCE" publication. Lastly, the fifth
> patch will introduce support for  "FOR ALL SEQUENCES IN SCHEMA"
> publication.
>
> I will work on this and share an updated patch for the same soon.

+1. Sounds like a good plan.

Regards,

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