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

On Mon, Aug 5, 2024 at 11:04 AM vignesh C <vignesh21@gmail.com> wrote:
>
> On Wed, 31 Jul 2024 at 14:39, shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Mon, Jun 10, 2024 at 5:00 PM vignesh C <vignesh21@gmail.com> wrote:
> > >
> > > On Mon, 10 Jun 2024 at 12:24, Amul Sul <sulamul@gmail.com> wrote:
> > > >
> > > >
> > > >
> > > > On Sat, Jun 8, 2024 at 6:43 PM vignesh C <vignesh21@gmail.com> wrote:
> > > >>
> > > >> On Wed, 5 Jun 2024 at 14:11, Amit Kapila <amit.kapila16@gmail.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.

thanks
Shveta