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

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.

Regards,
Vignesh