Re: Logical Replication of sequences - Mailing list pgsql-hackers
From | vignesh C |
---|---|
Subject | Re: Logical Replication of sequences |
Date | |
Msg-id | CALDaNm3dhK4VZLLAqg7BvewvruQ2B_OdfLgj0DeAXfd_iDBYLA@mail.gmail.com Whole thread Raw |
In response to | Re: Logical Replication of sequences (shveta malik <shveta.malik@gmail.com>) |
Responses |
Re: Logical Replication of sequences
|
List | pgsql-hackers |
On Thu, 26 Sept 2024 at 11:07, shveta malik <shveta.malik@gmail.com> wrote: > > On Fri, Sep 20, 2024 at 9:36 AM vignesh C <vignesh21@gmail.com> wrote: > > > > On Wed, 21 Aug 2024 at 11:54, vignesh C <vignesh21@gmail.com> wrote: > > > > > > On Wed, 21 Aug 2024 at 08:33, Peter Smith <smithpb2250@gmail.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?? This can occur if a checkpoint happened at that time. The regression test also has specific handling for this, as noted in a comment within the sequence.sql test file: -- log_cnt can be higher if there is a checkpoint just at the right -- time > 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? After executing REFRESH PUBLICATION SEQUENCES, the publication value will be resynchronized, and a new LSN will be generated and updated for the publisher sequence (using the old value). Therefore, this is not a concern. Regards, Vignesh
pgsql-hackers by date: