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

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.

Regards,
Vignesh