BUG #7710: Xid epoch is not updated properly during checkpoint - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7710
Logged by:          Tarvi Pillessaar
Email address:      tarvip@gmail.com
PostgreSQL version: 9.1.6
Operating system:   linux
Description:        =


This happens only if wal_level=3Dhot_standby.


Here are the steps to reproduce this issue.


We have following db cluster:

postgres@sbox /usr/local/pgsql $ pg_controldata data|grep NextXID
Latest checkpoint's NextXID:          0/4294966303
postgres@sbox /usr/local/pgsql $

Basically we have less than 1000 XIDs to epoch boundary.

Modify following parameters in conf:
checkpoint_segments =3D 16
checkpoint_completion_target =3D 0.9
checkpoint_timeout =3D 2min
log_checkpoints =3D on
log_line_prefix =3D '%t %r %p %d %u '
wal_level =3D hot_standby



Let's start up the cluster:
postgres@sbox /usr/local/pgsql $ postgres -D /usr/local/pgsql/data
2012-11-27 20:44:43 EET  26353   LOG:  database system was shut down at
2012-11-27 18:43:12 EET
2012-11-27 20:44:43 EET  26352   LOG:  database system is ready to accept
connections
...


In another session:
postgres@sbox /usr/local/pgsql $ psql -c "select now(),txid_current(),
txid_current()-2^32"
              now              | txid_current | ?column? =

-------------------------------+--------------+----------
 2012-11-27 20:45:01.394324+02 |   4294966303 |     -993



Now let's consume some XIDs, otherwise we have nothing to checkpoint.

postgres@sbox /usr/local/pgsql $ pgbench -c 1 -t 700
...
postgres@sbox /usr/local/pgsql $ psql -c "select now(),txid_current(),
txid_current()-2^32"
              now              | txid_current | ?column? =

-------------------------------+--------------+----------
 2012-11-27 20:45:27.256096+02 |   4294967005 |     -291



After a while, checkpoint starts:
2012-11-27 20:46:43 EET  26354   LOG:  checkpoint starting: time


Now let's cross the epoch boundary:

postgres@sbox /usr/local/pgsql $ pgbench -c 1 -t 700
...

postgres@sbox /usr/local/pgsql $ psql -c "select now(),txid_current(),
txid_current()-2^32"
              now              | txid_current | ?column? =

-------------------------------+--------------+----------
 2012-11-27 20:46:51.205384+02 |   4294967713 |      417

Seems that we have successfully crossed the boundary.


When checkpoint completes:
2012-11-27 20:47:32 EET  26354   LOG:  checkpoint complete: wrote 779
buffers (25.4%); 0 transaction log file(s) added, 0 removed, 9 recycled;
write=3D49.441 s, sync=3D0.170 s, total=3D49.636 s; sync files=3D16, longes=
t=3D0.131
s, average=3D0.010 s

postgres@sbox /usr/local/pgsql $ psql -c "select now(),txid_current(),
txid_current()-2^32"
              now              | txid_current |  ?column?   =

-------------------------------+--------------+-------------
 2012-11-27 20:47:42.031007+02 |          421 | -4294966875

It seems that epoch bump was rolled back.