Content-Type: multipart/alternative;
 boundary="------------O0nEeqRCYtGdJW1cEkPGkGXK"
Message-ID: <d864578b-b72f-2ab5-d1c8-d11703d91adc@pscs.co.uk>
Date: Tue, 15 Nov 2022 08:48:09 +0000
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0)
 Gecko/20100101 Thunderbird/102.4.2
Subject: Re: Sequence last value is different among primary and
 secondary
To: pgsql-admin@lists.postgresql.org
References: 
 <CAC0w7LJszznfQ7FmgPzFQBd=4t5y23cd0T1iU_D4g0+PG0X08w@mail.gmail.com>
From: Paul Smith <paul@pscs.co.uk>
In-Reply-To: 
 <CAC0w7LJszznfQ7FmgPzFQBd=4t5y23cd0T1iU_D4g0+PG0X08w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d864578b-b72f-2ab5-d1c8-d11703d91adc%40pscs.co.uk>
Precedence: bulk

This is a multi-part message in MIME format.
--------------O0nEeqRCYtGdJW1cEkPGkGXK
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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 | t
>
> This output belongs to the SECONDARY node:
>
> SELECT * FROM "ACCOUNT_ITEM_SEQ" ;
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>      105516 |       0 | t
>
> *There 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 Services
Tel: 01484 855800
Vat No: GB 685 6987 53

Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
--------------O0nEeqRCYtGdJW1cEkPGkGXK
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 15/11/2022 08:24, Kenny Bachman
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAC0w7LJszznfQ7FmgPzFQBd=4t5y23cd0T1iU_D4g0+PG0X08w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hello Team,
        <div><br>
        </div>
        <div>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.</div>
        <div><br>
        </div>
        <div>This sequence output belongs to the PRIMARY node:</div>
        <div><br>
        </div>
        <div>SELECT * FROM "ACCOUNT_ITEM_SEQ" ;<br>
           last_value | log_cnt | is_called <br>
          ------------+---------+-----------<br>
               105485 |      31 | t<br>
        </div>
        <div><br>
        </div>
        <div>This output belongs to the SECONDARY node:</div>
        <div><br>
        </div>
        <div>SELECT * FROM "ACCOUNT_ITEM_SEQ" ;<br>
           last_value | log_cnt | is_called <br>
          ------------+---------+-----------<br>
               105516 |       0 | t<br>
        </div>
        <div><br>
        </div>
        <div><b>There is no replication gap or lag between the nodes.</b></div>
        <div>What is the reason behind this difference?</div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>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.<br>
      <br>
      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')<br>
      <br>
      Eg, see
<a class="moz-txt-link-freetext" href="https://stackoverflow.com/questions/66456952/what-does-log-cnt-mean-in-the-postgres-sequence">https://stackoverflow.com/questions/66456952/what-does-log-cnt-mean-in-the-postgres-sequence</a><br>
      <br>
      Paul<br>
    </p>
  <div><br />
	
	</div>

<div>-- </div>

<div><br />
	
	</div>

<div><span style="font-weight: bold;">Paul Smith Computer Services</span></div>

<div>Tel: 01484 855800</div>

<div>Vat No: GB 685 6987 53</div>

<div><br />
	
	</div>

<div><a href="http://www.pscs.co.uk/go/subscribe">Sign up for news &amp; updates </a></div></body>
</html>


--------------O0nEeqRCYtGdJW1cEkPGkGXK--