Re: Logical Replication of sequences

On Mon, 5 Aug 2024 at 18:05, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Mon, Aug 5, 2024 at 11:04 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Wed, 31 Jul 2024 at 14:39, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > >
> > > On Mon, Jun 10, 2024 at 5:00 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > >
> > > > On Mon, 10 Jun 2024 at 12:24, Amul Sul <sulamul(at)gmail(dot)com> wrote:
> > > > >
> > > > >
> > > > >
> > > > > On Sat, Jun 8, 2024 at 6:43 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > > >>
> > > > >> On Wed, 5 Jun 2024 at 14:11, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > >> [...]
> > > > >> A new catalog table, pg_subscription_seq, has been introduced for
> > > > >> mapping subscriptions to sequences. Additionally, the sequence LSN
> > > > >> (Log Sequence Number) is stored, facilitating determination of
> > > > >> sequence changes occurring before or after the returned sequence
> > > > >> state.
> > > > >
> > > > >
> > > > > Can't it be done using pg_depend? It seems a bit excessive unless I'm missing
> > > > > something.
> > > >
> > > > We'll require the lsn because the sequence LSN informs the user that
> > > > it has been synchronized up to the LSN in pg_subscription_seq. Since
> > > > we are not supporting incremental sync, the user will be able to
> > > > identify if he should run refresh sequences or not by checking the lsn
> > > > of the pg_subscription_seq and the lsn of the sequence(using
> > > > pg_sequence_state added) in the publisher.
> > >
> > > How the user will know from seq's lsn that he needs to run refresh.
> > > lsn indicates page_lsn and thus the sequence might advance on pub
> > > without changing lsn and thus lsn may look the same on subscriber even
> > > though a sequence-refresh is needed. Am I missing something here?
> >
> > When a sequence is synchronized to the subscriber, the page LSN of the
> > sequence from the publisher is also retrieved and stored in
> > pg_subscriber_rel as shown below:
> > --- Publisher page lsn
> > publisher=# select pg_sequence_state('seq1');
> > pg_sequence_state
> > --------------------
> > (0/1510E38,65,1,t)
> > (1 row)
> >
> > --- Subscriber stores the publisher's page lsn for the sequence
> > subscriber=# select * from pg_subscription_rel where srrelid = 16384;
> > srsubid | srrelid | srsubstate | srsublsn
> > ---------+---------+------------+-----------
> > 16389 | 16384 | r | 0/1510E38
> > (1 row)
> >
> > If changes are made to the sequence, such as performing many nextvals,
> > the page LSN will be updated. Currently the sequence values are
> > prefetched for SEQ_LOG_VALS 32, so the lsn will not get updated for
> > the prefetched values, once the prefetched values are consumed the lsn
> > will get updated.
> > For example:
> > --- Updated LSN on the publisher (old lsn - 0/1510E38, new lsn - 0/1558CA8)
> > publisher=# select pg_sequence_state('seq1');
> > pg_sequence_state
> > ----------------------
> > (0/1558CA8,143,22,t)
> > (1 row)
> >
> > The user can then compare this updated value with the sequence's LSN
> > in pg_subscription_rel to determine when to re-synchronize the
> > sequence.
>
> Thanks for the details. But I was referring to the case where we are
> in between pre-fetched values on publisher (say at 25th value), while
> on subscriber we are slightly behind (say at 15th value), but page-lsn
> will be the same on both. Since the subscriber is behind, a
> sequence-refresh is needed on sub, but by looking at lsn (which is
> same), one can not say that for sure. Let me know if I have
> misunderstood it.

Yes, at present, if the value is within the pre-fetched range, we
cannot distinguish it solely using the page_lsn. However, the
pg_sequence_state function also provides last_value and log_cnt, which
can be used to handle these specific cases.

Regards,
Vignesh