Re: Logical Replication of sequences

On Thu, 13 Jun 2024 at 10:27, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Thu, Jun 13, 2024 at 10:10 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > > So, you're saying that when we synchronize the sequence values on the
> > > subscriber side, we will create a new relfilenode to allow reverting
> > > to the old state of the sequence in case of an error or transaction
> > > rollback? But why would we want to do that? Generally, even if you
> > > call nextval() on a sequence and then roll back the transaction, the
> > > sequence value doesn't revert to the old value. So, what specific
> > > problem on the subscriber side are we trying to avoid by operating on
> > > a new relfilenode?
> >
> > Let's consider a situation where we have two sequences: seq1 with a
> > value of 100 and seq2 with a value of 200. Now, let's say seq1 is
> > synced and updated to 100, then we attempt to synchronize seq2,
> > there's a failure due to the sequence not existing or encountering
> > some other issue. In this scenario, we don't want to halt operations
> > where seq1 is synchronized, but the sequence state for sequence isn't
> > changed to "ready" in pg_subscription_rel.
>
> Thanks for the explanation, but I am still not getting it completely,
> do you mean to say unless all the sequences are not synced any of the
> sequences would not be marked "ready" in pg_subscription_rel? Is that
> necessary? I mean why we can not sync the sequences one by one and
> mark them ready? Why it is necessary to either have all the sequences
> synced or none of them?

Since updating the sequence is one operation and setting
pg_subscription_rel is another, I was trying to avoid a situation
where the sequence is updated but its state is not reflected in
pg_subscription_rel. It seems you are suggesting that it's acceptable
for the sequence to be updated even if its state isn't updated in
pg_subscription_rel, and in such cases, the sequence value does not
need to be reverted.

Regards,
Vignesh