Get txid from pg_controldata’s output

背景

前面一篇BLOG介绍了txid和xid的区别，地址是

PostgreSQL xid(int4) to txid(int8)

不过还漏了一点东西，txid是怎么计算的。

注意xid最大是2^32次方。但是它实际上是会随时rotate的，即是一个起点（最老的没有被冻结的XID）不固定的圆圈（某些值被赋予特殊含义，例如2表示frozenXID）。

xid循环使用。而txid是没有rotate的，最大是2^63。假设平均一秒产生1亿个事务来算，txid可以使用2924年。所以基本不会超出。

PG社区也在想办法将XID升级为64位的。

我们来看一个数据库的pg_controldata输出 :

先看看控制文件的状态 :

# stat $PGDATA/global/pg_control  
  File: `$PGDATA/global/pg_control'  
  Size: 8192            Blocks: 24         IO Block: 4096   regular file  
Device: fd0bh/64779d    Inode: 93          Links: 1  
Access: (0600/-rw-------)  Uid: (  500/postgres)   Gid: (  500/postgres)  
Access: 2011-09-30 13:14:18.000000000 +0800  
Modify: 2011-09-30 13:13:29.000000000 +0800  
Change: 2011-09-30 13:13:29.000000000 +0800  

然后看看这个数据库的控制文件的输出 :

$ pg_controldata $PGDATA  
pg_control version number:            903  
Catalog version number:               201008051  
Database system identifier:           5521652928171532159  
Database cluster state:               in production  
pg_control last modified:             Fri 30 Sep 2011 01:13:29 PM CST  
Latest checkpoint location:           F28/930CE180  
Prior checkpoint location:            F28/58B49340  
Latest checkpoint's REDO location:    F28/6EAD81A8  
Latest checkpoint's TimeLineID:       1  
Latest checkpoint's NextXID:          2/3071633183  
Latest checkpoint's NextOID:          71959  
Latest checkpoint's NextMultiXactId:  1  
Latest checkpoint's NextMultiOffset:  0  
Latest checkpoint's oldestXID:        2954136504  
Latest checkpoint's oldestXID's DB:   50691  
Latest checkpoint's oldestActiveXID:  0  
Time of latest checkpoint:            Fri 30 Sep 2011 01:04:42 PM CST  
Minimum recovery ending location:     0/0  
Backup start location:                0/0  
Current wal_level setting:            minimal  
Current max_connections setting:      2000  
Current max_prepared_xacts setting:   0  
Current max_locks_per_xact setting:   64  
Maximum data alignment:               8  
Database block size:                  8192  
Blocks per segment of large relation: 1048576  
WAL block size:                       8192  
Bytes per WAL segment:                67108864  
Maximum length of identifiers:        64  
Maximum columns in an index:          32  
Maximum size of a TOAST chunk:        1996  
Date/time type storage:               64-bit integers  
Float4 argument passing:              by value  
Float8 argument passing:              by value  

注意这行记录 :

Latest checkpoint's NextXID:          2/3071633183  

然后到数据库看看现在的txid :

postgres@db_digoal-> psql -h 127.0.0.1 digoal postgres  
psql (9.0.5)  
Type "help" for help.  
digoal=# select txid_current();  
 txid_current   
--------------  
  11662043793  
(1 row)  

然后要做的是来一个计算 :

Latest checkpoint's NextXID:          2/3071633183  

这里的2表示xid已经rotate两次了,你可以去看你刚刚建好的库，这个值肯定是0。

3071633183就是rotate两次后的值，而txid是不会被rotate的，所以下面的计算就能说明一切了。

digoal=# select 2^32*2+3071633183;  
  ?column?     
-------------  
 11661567775  
(1 row)  
  
digoal=# select 11662043793-11661567775;  
 ?column?   
----------  
   476018  
(1 row)  

可能经过下面的操作之后，会更加清晰 :

digoal=# checkpoint;  select txid_current();  
CHECKPOINT  
 txid_current   
--------------  
  11662195953  
(1 row)  
  
postgres@db_digoal-> pg_controldata   
pg_control version number:            903  
Catalog version number:               201008051  
Database system identifier:           5521652928171532159  
Database cluster state:               in production  
pg_control last modified:             Fri 30 Sep 2011 01:26:24 PM CST  
Latest checkpoint location:           F28/C357FAA8  
Prior checkpoint location:            F28/C2B99060  
Latest checkpoint's REDO location:    F28/C34E18E8  
Latest checkpoint's TimeLineID:       1  
Latest checkpoint's NextXID:          2/3072260499  
Latest checkpoint's NextOID:          71959  
Latest checkpoint's NextMultiXactId:  1  
Latest checkpoint's NextMultiOffset:  0  
Latest checkpoint's oldestXID:        2954136504  
Latest checkpoint's oldestXID's DB:   50691  
Latest checkpoint's oldestActiveXID:  0  
Time of latest checkpoint:            Fri 30 Sep 2011 01:26:23 PM CST  
Minimum recovery ending location:     0/0  
Backup start location:                0/0  
Current wal_level setting:            minimal  
Current max_connections setting:      2000  
Current max_prepared_xacts setting:   0  
Current max_locks_per_xact setting:   64  
Maximum data alignment:               8  
Database block size:                  8192  
Blocks per segment of large relation: 1048576  
WAL block size:                       8192  
Bytes per WAL segment:                67108864  
Maximum length of identifiers:        64  
Maximum columns in an index:          32  
Maximum size of a TOAST chunk:        1996  
Date/time type storage:               64-bit integers  
Float4 argument passing:              by value  
Float8 argument passing:              by value  

再来计算 :

select (2^32)*2+3072260499;  
  ?column?     
-------------  
 11662195091  
(1 row)  

和11662195953相差862，因为数据库比较繁忙。

详见源码部分，实际上是2是epoch的意思，即XID已经用了几轮了？

参考

man pg_controldata

PostgreSQL xid(int4) to txid(int8)

digoal’s 大量PostgreSQL文章入口