Re: Logical Replication of sequences

On Wed, 7 Aug 2024 at 08:09, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Aug 6, 2024 at 5:13 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > 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.
> >
>
> This makes sense to me.
>
> >
> > However, the
> > pg_sequence_state function also provides last_value and log_cnt, which
> > can be used to handle these specific cases.
> >
>
> BTW, can we document all these steps for users to know when to refresh
> the sequences, if not already documented?

This has been documented in the v20240807 version attached at [1].
[1] - https://www.postgresql.org/message-id/CALDaNm01Z6Oo9osGMFTOoyTR1kVoyh1rEvZ%2B6uJn-ZymV%3D0dbQ%40mail.gmail.com

Regards,
Vignesh