txid_snapshot的文本表示为:xmin:xmax:xip_list。
名称描述 xmin 最早的事务ID(txid)仍然活动。所有较早事务将是已经提交可见的,或者是直接回滚。xmax 作为尚未分配的txid。所有大于或等于此txids的都是尚未开始的快照时间,因此不可见。xip_list当前快照中活动的txids。这个列表只包含在xmin和xmax之间活动的txids;有可能活动的txids高于xmax。 介于大于等于xmin、小于xmax,并且不在这个列表中的txid,在这个时间快照已经完成的,因此按照提交状态查看他是可见还是回滚。这个列表不包含子事务的txids。
示例:10:20:10,13,15意思为:xmin=10, xmax=20, xip_list=10, 13, 15。
测试如下:
1.通过设置强制对临时对象使用COMMIT而不是2PC
SET enforce_two_phase_commit TO off;
2.正常案例演示
postgres=# select '12:13:'::txid_snapshot;## txid_snapshot12:13:(1 row)postgres=# select '12:18:14,16'::txid_snapshot;## txid_snapshot12:18:14,16(1 row)
3.错误案例演示
postgres=# select '31:12:'::txid_snapshot;ERROR: invalid input for txid_snapshot: "31:12:"LINE 1: select '31:12:'::txid_snapshot;^CONTEXT: referenced column: txid_snapshot
-------------------------------------------------------------------------------
postgres=# select '0:1:'::txid_snapshot;ERROR: invalid input for txid_snapshot: "0:1:"LINE 1: select '0:1:'::txid_snapshot;^CONTEXT: referenced column: txid_snapshot
-------------------------------------------------------------------------------
postgres=# select '12:13:0'::txid_snapshot;ERROR: invalid input for txid_snapshot: "12:13:0"LINE 1: select '12:13:0'::txid_snapshot;^CONTEXT: referenced column: txid_snapshot
-------------------------------------------------------------------------------
postgres=# select '12:16:14,13'::txid_snapshot;ERROR: invalid input for txid_snapshot: "12:16:14,13"LINE 1: select '12:16:14,13'::txid_snapshot;^CONTEXT: referenced column: txid_snapshot
-------------------------------------------------------------------------------
postgres=# select '12:16:14,14'::txid_snapshot;ERROR: invalid input for txid_snapshot: "12:16:14,14"LINE 1: select '12:16:14,14'::txid_snapshot;^CONTEXT: referenced column: txid_snapshot
通过测试看出xmax应该大于xmin,不可为0,tixds应该按增序排列,且不为0,并且不能有重复的tixds,在使用的时候应当尽量避免。
4.创建测试表及测试数据导入
postgres=# create temp table snapshot_test(nr integer,snap txid_snapshot);CREATE TABLEpostgres=# insert into snapshot_test values (1, '12:13:');INSERT 0 1postgres=# insert into snapshot_test values (2, '12:20:13,15,18');INSERT 0 1postgres=# insert into snapshot_test values (3, '100001:100009:100005,100007,100008');INSERT 0 1postgres=# insert into snapshot_test values (4, '100:150:101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131');INSERT 0 1
查询数据情况:
postgres=# select snap from snapshot_test order by nr;snap-------------------------------------------------------------------------------------------------------------------------------------12:13:12:20:13,15,18100001:100009:100005,100007,100008100:150:101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131(4 rows)
5.函数测试
txid_snapshot_xmin()为会返回快照的xmin,
txid_snapshot_xmax()会返回快照的xmax,
txid_snapshot_xip()获取正在进行的事务ip,即txids。
postgres=# select txid_snapshot_xmin(snap),postgres-# txid_snapshot_xmax(snap),postgres-# txid_snapshot_xip(snap)postgres-# from snapshot_test order by nr, 1, 2, 3;txid_snapshot_xmin | txid_snapshot_xmax | txid_snapshot_xip--------------------+--------------------+-------------------12 | 20 |1312 | 20 |1512 | 20 |18100001 | 100009 | 100005100001 | 100009 | 100007100001 | 100009 | 100008100 |150 |101100 |150 |102100 |150 |103100 |150 |104100 |150 |105
txid_visible_in_snapshot()会查看在快照中事务ID是否可见(不使用子事务ID)
postgres=# select id, txid_visible_in_snapshot(id, snap)postgres-# from snapshot_test, generate_series(11, 21) idpostgres-# where nr = 2;id | txid_visible_in_snapshot----+--------------------------11 | t12 | t13 | f14 | t15 | f16 | t17 | t18 | f19 | t20 | f21 | f(11 rows)
6.其他测试
测试二分查找
postgres=# select id, txid_visible_in_snapshot(id, snap)postgres-# from snapshot_test, generate_series(90, 160) idpostgres-# where nr = 4;id | txid_visible_in_snapshot-----+--------------------------90 | t91 | t92 | t93 | t94 | t95 | t96 | t97 | t98 | t99 | t100 | t101 | f
测试当前值
postgres=# select txid_current() >= txid_snapshot_xmin(txid_current_snapshot());## ?column?t(1 row)
我们不能假设当前值总是小于xmax
postgres=# select txid_visible_in_snapshot(txid_current(), txid_current_snapshot());## txid_visible_in_snapshotf(1 row)
测试64bitness(MOGDB/openGauss将transactionid由int32改为了int64,64位的xid永远不可能耗尽,虽然xid改为了64位,但是过期的xid依旧需要freeze清理,只是永远不用担心会发生xid回卷宕机的风险。 )
postgres=# select txid_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013';## txid_snapshot1000100010001000:1000100010001100:1000100010001012,1000100010001013(1 row)postgres=# select txid_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');## txid_visible_in_snapshotf(1 row)postgres=# select txid_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');## txid_visible_in_snapshott(1 row)
测试溢出64bit,9223372036854775807是是263-1,是乘方 也就是63位的最大二进制数字 。
postgres=# SELECT txid_snapshot '1:9223372036854775807:3';## txid_snapshot1:9223372036854775807:3(1 row)postgres=# SELECT txid_snapshot '1:9223372036854775808:3';ERROR: invalid input for txid_snapshot: "1:9223372036854775808:3"LINE 1: SELECT txid_snapshot '1:9223372036854775808:3';^CONTEXT: referenced column: txid_snapshot