Re: Logical Replication of sequences

On Fri, Sep 20, 2024 at 9:36 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Wed, 21 Aug 2024 at 11:54, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Wed, 21 Aug 2024 at 08:33, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > >
> > > Hi Vignesh, Here are my only review comments for the latest patch set.
> >
> > Thanks, these issues have been addressed in the updated version.
> > Additionally, I have fixed the pgindent problems that were reported
> > and included another advantage of this design in the file header of
> > the sequencesync file.
>
> The patch was not applied on top of head, here is a rebased version of
> the patches.
> I have also removed an invalidation which was not required for
> sequences and a typo.
>

Thank You for the patches. I would like to understand srsublsn and
page_lsn more. Please see the scenario below:

I have a sequence:
CREATE SEQUENCE myseq0 INCREMENT 5 START 100;

After refresh on sub:
postgres=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
ALTER SUBSCRIPTION

postgres=# select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+-----------
16385 | 16384 | r | 0/152F380 -->pub's page_lsn

postgres=# select * from pg_sequence_state('myseq0');
page_lsn | last_value | log_cnt | is_called
-----------+------------+---------+-----------
0/152D830 | 105 | 31 | t -->(I am assuming 0/152D830 is
local page_lsn corresponding to value-=105)

Now I assume that *only* after doing next_wal for 31 times, page_lsn
shall change. But I observe strange behaviour

After running nextval on sub for 7 times:
postgres=# select * from pg_sequence_state('myseq0');
page_lsn | last_value | log_cnt | is_called
-----------+------------+---------+-----------
0/152D830 | 140 | 24 | t -->correct

After running nextval on sub for 15 more times:
postgres=# select * from pg_sequence_state('myseq0');
page_lsn | last_value | log_cnt | is_called
-----------+------------+---------+-----------
0/152D830 | 215 | 9 | t -->correct
(1 row)

Now after running it 6 more times:
postgres=# select * from pg_sequence_state('myseq0');
page_lsn | last_value | log_cnt | is_called
-----------+------------+---------+-----------
0/152D990 | 245 | 28 | t --> how??

last_value increased in the expected way (6*5), but page_lsn changed
and log_cnt changed before we could complete the remaining runs as
well. Not sure why??

Now if I do refresh again:

postgres=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
ALTER SUBSCRIPTION

postgres=# select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+-----------
16385 | 16384 | r | 0/152F380-->pub's page_lsn, same as old one.

postgres=# select * from pg_sequence_state('myseq0');
page_lsn | last_value | log_cnt | is_called
-----------+------------+---------+-----------
0/152DDB8 | 105 | 31 | t
(1 row)

Now, what is this page_lsn = 0/152DDB8? Should it be the one
corresponding to last_value=105 and thus shouldn't it match the
previous value of 0/152D830?

thanks
Shveta