Обсуждение: Sequence last value is different among primary and secondary
last_value | log_cnt | is_called
------------+---------+-----------
105485 | 31 | t
last_value | log_cnt | is_called
------------+---------+-----------
105516 | 0 | t
Hello Team,I got a weird situation about the sequence value in PG 14.5 (rhel 8.6). The sequence's last value is different between primary and secondary nodes.This sequence output belongs to the PRIMARY node:SELECT * FROM "ACCOUNT_ITEM_SEQ" ;
last_value | log_cnt | is_called
------------+---------+-----------
105485 | 31 | tThis output belongs to the SECONDARY node:SELECT * FROM "ACCOUNT_ITEM_SEQ" ;
last_value | log_cnt | is_called
------------+---------+-----------
105516 | 0 | tThere is no replication gap or lag between the nodes.What is the reason behind this difference?
I believe this is to do with the way PostgreSQL does sequences. It doesn't update the WAL every time a sequence increments, just every 32 times to avoid writing lots of unnecessary entries given that the sequence generally updates in a predictable manner. The 'log_cnt' keeps track of how many increments are to go before the next WAL update.
I guess the secondary is just updated with the next 'step', so that if there's a failover, the sequence number is at a 'safe' position without causing duplicates. (note that the primary 'last_value + log_cnt' = secondary 'last_value')
Eg, see https://stackoverflow.com/questions/66456952/what-does-log-cnt-mean-in-the-postgres-sequence
Paul
On 15/11/2022 08:24, Kenny Bachman wrote:Hello Team,I got a weird situation about the sequence value in PG 14.5 (rhel 8.6). The sequence's last value is different between primary and secondary nodes.This sequence output belongs to the PRIMARY node:SELECT * FROM "ACCOUNT_ITEM_SEQ" ;
last_value | log_cnt | is_called
------------+---------+-----------
105485 | 31 | tThis output belongs to the SECONDARY node:SELECT * FROM "ACCOUNT_ITEM_SEQ" ;
last_value | log_cnt | is_called
------------+---------+-----------
105516 | 0 | tThere is no replication gap or lag between the nodes.What is the reason behind this difference?
I believe this is to do with the way PostgreSQL does sequences. It doesn't update the WAL every time a sequence increments, just every 32 times to avoid writing lots of unnecessary entries given that the sequence generally updates in a predictable manner. The 'log_cnt' keeps track of how many increments are to go before the next WAL update.
I guess the secondary is just updated with the next 'step', so that if there's a failover, the sequence number is at a 'safe' position without causing duplicates. (note that the primary 'last_value + log_cnt' = secondary 'last_value')
Eg, see https://stackoverflow.com/questions/66456952/what-does-log-cnt-mean-in-the-postgres-sequence
Paul--Paul Smith Computer ServicesTel: 01484 855800Vat No: GB 685 6987 53