mvcc调试看系统字段更新
查询系统字段
select attname, attnum, atttypid::regtype, attisdropped::text from pg_attribute where attrelid = ‘t1‘::regclass;
attname | attnum | atttypid | attisdropped
----------+--------+-------------------+--------------
tableoid | -6 | oid | false
cmax | -5 | cid | false
xmax | -4 | xid | false
cmin | -3 | cid | false
xmin | -2 | xid | false
ctid | -1 | tid | false
id | 1 | integer | false
c1 | 2 | character varying | false
tableoid-数据表OID
cmax-删除该tuple的事务内部命令ID
xmax-删除该tuple的事务ID
cmin-插入该tuple的事务内部命令ID
xmin-插入该tuple的事务ID
ctid-heap tuple的ID
insert
create table t2 (id int,c1 varchar(40)) ;
insert into t2 values(1,‘first‘);
insert into t2 values(2,‘second‘);
postgres=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_page_items(get_raw_page(‘t2‘,0));
lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask
----+--------+----------+--------+--------+-------+--------+-------------+------------
1 | 8152 | 1 | 41346 | 0 | 0 | (0,1) | 2 | 2050
2 | 8112 | 1 | 41347 | 0 | 0 | (0,2) | 2 | 2050
以第一行举例
lp：linePointer，行指针
t_xmin：41346，插入数据的事务id
t_xmax: 0, invalid事务
t_cid：heap tuple id
t_infomask2：2，有2个字段
t_infomast：2050，0x0802,标记存在可变长属性（HEAP_HASVARWIDTH）
10 #define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */
100000000000 #define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */
更新
提交
postgres=# update t2 set c1=‘first#version2‘ where id = 1;
UPDATE 1
postgres=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_page_items(get_raw_page(‘t2‘,0));
lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask
----+--------+----------+--------+--------+-------+--------+-------------+------------
1 | 8152 | 1 | 41346 | 41348 | 0 | (0,3) | 16386 | 258
2 | 8112 | 1 | 41347 | 0 | 0 | (0,2) | 2 | 2306
3 | 8064 | 1 | 41348 | 0 | 0 | (0,3) | 32770 | 10242
变化点：
第一行
1）新增了t_xmax
2）t_ctid指向新的heap tuple
3）t_infomask2变为16386，即0x4002 -> HEAP_HOT_UPDATED
4）t_infomask是258,即0x0102 -> HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH
第三行
1）t_infomask2是32770,即0x8002 -> HEAP_ONLY_TUPLE
t_infomask是10242,即0x2802 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH
回滚
begin;
update t2 set c1=‘first#version3‘ where id = 1;
rollback;
select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_page_items(get_raw_page(‘t2‘,0));
lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask
----+--------+----------+--------+--------+-------+--------+-------------+------------
1 | 8152 | 1 | 41346 | 41348 | 0 | (0,3) | 16386 | 1282
2 | 8112 | 1 | 41347 | 0 | 0 | (0,2) | 2 | 2306
3 | 8064 | 1 | 41348 | 41349 | 0 | (0,4) | 49154 | 8450
4 | 8016 | 1 | 41349 | 0 | 0 | (0,4) | 32770 | 10242
变化点：
第三行
t_xmax设置为更新事务的ID
t_infomask2=49154,即0xC002
t_infomask=8450,即0x2102 -> HEAP_UPDATED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH
第四行
t_infomask2=32770,即0x8002 -> HEAP_ONLY_TUPLE
t_infomask=10242,即0x2802 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH
跟提交事务看上去一样
删除
提交
begin;
delete from t2 where id = 1;
commit;
postgres=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_page_items(get_raw_page(‘t2‘,0));
lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask
----+--------+----------+--------+--------+-------+--------+-------------+------------
1 | 8152 | 1 | 41346 | 41348 | 0 | (0,3) | 16386 | 1282
2 | 8112 | 1 | 41347 | 0 | 0 | (0,2) | 2 | 2306
3 | 8064 | 1 | 41348 | 41350 | 0 | (0,3) | 40962 | 8450
4 | 8016 | 1 | 41349 | 0 | 0 | (0,4) | 32770 | 10754
变化点：
第三行
tuple被删除
t_xmax修改为41350
t_ctid修改为(0,3)
t_infomask2=40962,即0xA002
t_infomask=9474,即0x2502 -> HEAP_UPDATED | HEAP_XMAX_COMMITTED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH
回滚
begin;
delete from t2 where id = 2;
rollback;
postgres=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_page_items(get_raw_page(‘t2‘,0));
lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask
----+--------+----------+--------+--------+-------+--------+-------------+------------
1 | 8152 | 1 | 41346 | 41348 | 0 | (0,3) | 16386 | 1282
2 | 8112 | 1 | 41347 | 41351 | 0 | (0,2) | 8194 | 258
3 | 8064 | 1 | 41348 | 41350 | 0 | (0,3) | 40962 | 9474
4 | 8016 | 1 | 41349 | 0 | 0 | (0,4) | 32770 | 10754
变化点：
第二行
t_max修改为41351
t_infomask2=8194
t_infomask=258
infomask2标记
一共16位，五种情况
#define HEAP_NATTS_MASK 0x07FF /* 11 bits for number of attributes */
//低11位为属性个数
/* bits 0x1800 are available */
#define HEAP_KEYS_UPDATED 0x2000 /* tuple was updated and key cols
* modified, or tuple deleted */
#define HEAP_HOT_UPDATED 0x4000 /* tuple was HOT-updated */
#define HEAP_ONLY_TUPLE 0x8000 /* this is heap-only tuple */
#define HEAP2_XACT_MASK 0xE000 /* visibility-related bits */
//把十六进制值转换为二进制显示
11111111111 #define HEAP_NATTS_MASK 0x07FF
10000000000000 #define HEAP_KEYS_UPDATED 0x2000
100000000000000 #define HEAP_HOT_UPDATED 0x4000
1000000000000000 #define HEAP_ONLY_TUPLE 0x8000
1110000000000000 #define HEAP2_XACT_MASK 0xE000
1111111111111110 #define SpecTokenOffsetNumber 0xfffe
infomask标记
也是16位，十几种含义，组合
1 #define HEAP_HASNULL 0x0001 /* has null attribute(s) */
10 #define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */
100 #define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */
1000 #define HEAP_HASOID 0x0008 /* has an object-id field */
10000 #define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */
100000 #define HEAP_COMBOCID 0x0020 /* t_cid is a combo cid */
1000000 #define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */
10000000 #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)
100000000 #define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */
1000000000 #define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
10000000000 #define HEAP_XMAX_COMMITTED 0x0400 /* t_xmax committed */
100000000000 #define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */
1000000000000 #define HEAP_XMAX_IS_MULTI 0x1000 /* t_xmax is a MultiXactId */
10000000000000 #define HEAP_UPDATED 0x2000 /* this is UPDATEd version of row */
100000000000000 #define HEAP_MOVED_OFF 0x4000 /* moved to another place by pre-9.0
* VACUUM FULL; kept for binary
* upgrade support */
1000000000000000 #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)
1111111111110000 #define HEAP_XACT_MASK 0xFFF0 /* visibility-related bits */
转自
原文：https://www.cnblogs.com/jiangshifu/p/15187611.html