PG 9.4 行冻结信息的测试-CSDN博客

有在网上找过一些关于vacuum_freeze_min_age参数是如何影响vacuum冻结旧行的文章。类似的参考资料可以参看链接：
http://blog.csdn.net/luojinbai/article/details/45848381


但是自己在做类似的测试时发现，并没有出现相同的结果，反复测试几次，仍然不同。后来咨询了下德哥说，看源码，行冻结的信息不再通过改变xmin来实现行冻结的信息。当时就觉得绝望了，对于一个完全不懂任何高级开发语言的人来说，这个等于对PG又多了个谜。后来想到，是不是可以通过查看page中的信息，来确认，冻结的信息到底是怎么反应到infomask中。最后想到还从来没有用过pageinspect呢。


看了下官网对pageinspect的介绍：
https://www.postgresql.org/docs/9.4/static/pageinspect.html
可以通过函数heap_page_items(page bytea)来查看行头部的信息内容。非常开心的发现，在这个章节中，里面有很负责任地指出可以在那些源码文件中找到相关的信息，接下来，就开始了我的第一次源码查找的经历。

官网有如下一段话：
See src/include/storage/itemid.h and src/include/access/htup_details.h for explanations of the fields returned.

看来这个就是关于行的详细信息了。因为知道冻结信息放在t_infomask中，所以，找到htup_details.sh后，先定位t_infomask的结构，有如下内容：

/*
 * information stored in t_infomask:
 */
#define HEAP_HASNULL            0x0001    /* has null attribute(s) */
#define HEAP_HASVARWIDTH        0x0002    /* has variable-width attribute(s) */
#define HEAP_HASEXTERNAL        0x0004    /* has external stored attribute(s) */
#define HEAP_HASOID                0x0008    /* has an object-id field */
#define HEAP_XMAX_KEYSHR_LOCK    0x0010    /* xmax is a key-shared locker */
#define HEAP_COMBOCID            0x0020    /* t_cid is a combo cid */
#define HEAP_XMAX_EXCL_LOCK        0x0040    /* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY        0x0080    /* xmax, if valid, is only a locker */

 /* xmax is a shared locker */
#define HEAP_XMAX_SHR_LOCK    (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)

#define HEAP_LOCK_MASK    (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \
                         HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_XMIN_COMMITTED        0x0100    /* t_xmin committed */
#define HEAP_XMIN_INVALID        0x0200    /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN        (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED        0x0400    /* t_xmax committed */
#define HEAP_XMAX_INVALID        0x0800    /* t_xmax invalid/aborted */
#define HEAP_XMAX_IS_MULTI        0x1000    /* t_xmax is a MultiXactId */
#define HEAP_UPDATED            0x2000    /* this is UPDATEd version of row */
#define HEAP_MOVED_OFF            0x4000    /* moved to another place by pre-9.0
                                         * VACUUM FULL; kept for binary
                                         * upgrade support */
#define HEAP_MOVED_IN            0x8000    /* moved from another place by pre-9.0
                                         * VACUUM FULL; kept for binary
                                         * upgrade support */
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)

#define HEAP_XACT_MASK            0xFFF0    /* visibility-related bits */


其中关于冻结的信息就存放在HEAP_XMIN_FROZEN中了。而该部分信息时通过另外两个字段相与得出的：
HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID

那么如果某个行被冻结后，那么该位的值应该是3. 接下来进行测试。



新建一张表，并插入一条数据，同时查看通过age以及pageinspect的函数查看相关的信息：
test=# create table t (id int,vb text);
CREATE TABLE
test=# insert into t values (1,'test');
INSERT 0 1
test=# select ctid,age(xmin),xmin,xmax,* from t;
 ctid  | age | xmin | xmax | id |  vb  
-------+-----+------+------+----+------
 (0,1) |   1 | 1864 |    0 |  1 | test
(1 row)

test=# select * from heap_page_items(get_raw_page('t',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid 
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------
  1 |   8152 |        1 |     33 |   1864 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |      
(1 row)

t_infomask对应的的2306，是t_infomask对应的值，该值是十进制，转换成十六进制则是0x0902，对应的位信息是：
HEAP_HASVARWIDTH
HEAP_XMIN_COMMITTED
HEAP_XMAX_INVALID



插入第二条数据，让表里的数据行的年龄有些不同：
test=# insert into t values (2,'again test');
INSERT 0 1


刚刚插入行的t_infomask值2050对应的十六进制为0x0802，之所以没有HEAP_XMIN_COMMITTED位信息的原因是，关于事务状态信息是通过hint bits来体现的，刚刚插入的行并不会在事务结束时立刻更新hint bits，而是会在下一次查询或者更新等操作时才会设置，所以这才出现了没有HEAP_XMIN_COMMITTED信息的t_infomask，具体的文章介绍，可以查看另一片翻译文章：
https://yq.aliyun.com/articles/55682

test=# select * from heap_page_items(get_raw_page('t',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid 
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------
  1 |   8152 |        1 |     33 |   1864 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |      
  2 |   8112 |        1 |     39 |   1865 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |      
(2 rows)

test=# select ctid,age(xmin),xmin,xmax,* from t;
 ctid  | age | xmin | xmax | id |     vb     
-------+-----+------+------+----+------------
 (0,1) |   2 | 1864 |    0 |  1 | test
 (0,2) |   1 | 1865 |    0 |  2 | again test
(2 rows)

当通过查询语句select ctid,age(xmin),xmin,xmax,* from t查看表中数据后，hint bits得到更新，此时的t_infomask变成了2306，即0x0902
test=# select * from heap_page_items(get_raw_page('t',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid 
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------
  1 |   8152 |        1 |     33 |   1864 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |      
  2 |   8112 |        1 |     39 |   1865 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |      
(2 rows)



查看当前pg_class中relfrozenxid字段的值，随后该值会因为vacuum的触发而改变。
test=# select relfrozenxid from pg_class where relname= 't';
 relfrozenxid 
--------------
         1863
(1 row)

将vacuum_freeze_min_age设置成更小的值，方便查看在经过很少的事务之后，就能使得表中的行可以被冻结。
test=# set vacuum_freeze_min_age=5;
SET
test=# select txid_current();
 txid_current 
--------------
         1866
(1 row)

test=# select txid_current();
 txid_current 
--------------
         1867
(1 row)

test=# select txid_current();
 txid_current 
--------------
         1868
(1 row)

test=# select relfrozenxid from pg_class where relname= 't';
 relfrozenxid 
--------------
         1863
(1 row)

test=# select ctid,age(xmin),xmin,xmax,* from t;
 ctid  | age | xmin | xmax | id |     vb     
-------+-----+------+------+----+------------
 (0,1) |   5 | 1864 |    0 |  1 | test
 (0,2) |   4 | 1865 |    0 |  2 | again test
(2 rows)

test=# vacuum t;
VACUUM
test=# select ctid,age(xmin),xmin,xmax,* from t;
 ctid  | age | xmin | xmax | id |     vb     
-------+-----+------+------+----+------------
 (0,1) |   5 | 1864 |    0 |  1 | test
 (0,2) |   4 | 1865 |    0 |  2 | again test
(2 rows)

test=# select * from heap_page_items(get_raw_page('t',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid 
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------
  1 |   8152 |        1 |     33 |   1864 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |      
  2 |   8112 |        1 |     39 |   1865 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |      
(2 rows)

以上信息没有发生变化，还需要再次执行一次txid_current函数。



test=# select relfrozenxid from pg_class where relname= 't';
 relfrozenxid 
--------------
         1864
(1 row)

test=# select txid_current();
 txid_current 
--------------
         1869
(1 row)

test=# vacuum t;
VACUUM
test=# select * from heap_page_items(get_raw_page('t',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid 
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------
  1 |   8152 |        1 |     33 |   1864 |      0 |        0 | (0,1)  |           2 |       2818 |     24 |        |      
  2 |   8112 |        1 |     39 |   1865 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |      
(2 rows)
此时可以发现，age为6的ctid为(0,1)的行的t_infomask值发生了改变，2818转换后是0x0b02，对应的位信息为：
HEAP_XMIN_FROZEN
HEAP_XMAX_INVALID
HEAP_HASVARWIDTH
这个时候就可以判断，t中的行被冻结了。


此时，relfrozenxid也发生了变化，该字段实际值，根据官方文档的描述：
    All transaction IDs before this one have been replaced with a permanent ("frozen") transaction ID in this table. This is used to track whether the table needs to be vacuumed in order to prevent transaction ID wraparound or to allow pg_clog to be shrunk.
根据自己的理解，该字段存放的是上一次全表vacuum的冻结截止XID，可能会有些偏差～～。
test=# select relfrozenxid from pg_class where relname= 't';
 relfrozenxid 
--------------
         1865
(1 row)

test=# select ctid,age(xmin),xmin,xmax,* from t;
 ctid  | age | xmin | xmax | id |     vb     
-------+-----+------+------+----+------------
 (0,1) |   6 | 1864 |    0 |  1 | test
 (0,2) |   5 | 1865 |    0 |  2 | again test
(2 rows)


尽管冻结了行， 但是并没有体现在xmin中，也就印证了冻结的信息不再通过xmin来体现，源码中也有类似的介绍：

/*
 * HeapTupleHeaderGetRawXmin returns the "raw" xmin field, which is the xid
 * originally used to insert the tuple.  However, the tuple might actually
 * be frozen (via HeapTupleHeaderSetXminFrozen) in which case the tuple's xmin
 * is visible to every snapshot.  Prior to PostgreSQL 9.4, we actually changed
 * the xmin to FrozenTransactionId, and that value may still be encountered
 * on disk.
 */