Re: Logical Replication of sequences

On Fri, 9 Aug 2024 at 12:13, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Wed, Aug 7, 2024 at 2:00 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > 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
> >
>
> Vignesh, I looked at the patch dated 240808, but I could not find
> these steps. Are you referring to the section ' Examples:
> Synchronizing Sequences Between Publisher and Subscriber' in doc
> patch004? If not, please point me to the concerned section.

I'm referring to the "Refreshing Stale Sequences" part in the
v20240809 version patch attached at [1] which only mentions directly
comparing the sequence values.. I have removed the reference to
pg_sequence_state now as suggesting that users use pg_sequence_state
and sequence when page_lsn seems complex, the same can be achieved by
comparing the sequence values from a single statement instead of a
couple of statements. Peter had felt this would be easier based on
comment 3c at [1].

[1] - https://www.postgresql.org/message-id/CALDaNm0LJCtGoBCO6DFY-RDjR8vxapW3W1f7%3D-LSQx%3DXYjqU%3Dw%40mail.gmail.com

Regards,
Vignesh