個(gè)人中心
退出
PostgreSQL 中的系統(tǒng)字段：tableoid、xmin、xmax、cmin、cmax、ctid
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
tableoid
ctid
xmin
xmax
cmin
cmax
oid
總結(jié)
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來(lái)談?wù)?PostgreSQL 數(shù)據(jù)表中幾個(gè)隱藏的系統(tǒng)字段和它們的作用。
在 PostgreSQL 中，當(dāng)我們創(chuàng)建一個(gè)數(shù)據(jù)表時(shí)，數(shù)據(jù)庫(kù)會(huì)隱式增加幾個(gè)系統(tǒng)字段。這些字段由系統(tǒng)進(jìn)行維護(hù)，用戶一般不會(huì)感知它們的存在。例如，以下語(yǔ)句創(chuàng)建了一個(gè)簡(jiǎn)單的表：
create table test(col integer);
insert into test(col)
values (1),(2),(3);
從定義上來(lái)看，表 test 中只有一個(gè)字段；但是當(dāng)我們查詢數(shù)據(jù)字典表 pg_attribute 時(shí)，結(jié)果卻不是如此：
hrdb=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
hrdb=> select attname, attnum, atttypid::regtype
hrdb-> from pg_attribute
hrdb-> where attrelid = 'test'::regclass;
attname | attnum | atttypid
----------+--------+----------
tableoid | -6 | oid
cmax | -5 | cid
xmax | -4 | xid
cmin | -3 | cid
xmin | -2 | xid
ctid | -1 | tid
col | 1 | integer
(7 rows)
查詢結(jié)果顯示，表 test 中一共包含 7 個(gè)字段。PostgreSQL 為我們?cè)黾恿?6 個(gè)額外的系統(tǒng)字段，它們的 attnum 屬性都是負(fù)數(shù)。
下面讓我們分別看看這些系統(tǒng)字段的作用。
tableoid
tableoid 字段代表了數(shù)據(jù)所在表的對(duì)象 id（OID），也就是數(shù)據(jù)字典表 pg_class 中與該表信息相關(guān)的數(shù)據(jù)行。
hrdb=> select oid, relname from pg_class where relname = 'test';
oid | relname
-------+---------
90277 | test
(1 row)
hrdb=> select t.tableoid, t.col, c.relname
hrdb-> from test t
hrdb-> join pg_class c on (c.oid = t.tableoid);
tableoid | col | relname
----------+-----+---------
90277 | 1 | test
90277 | 2 | test
90277 | 3 | test
(3 rows)
tableoid 的另一個(gè)用途就是在涉及分區(qū)表查詢或者 UNION 操作時(shí)標(biāo)識(shí)數(shù)據(jù)行所在的具體表。例如存在以下分區(qū)表：
create table part_t
(id integer) partition by hash (id);
create table part_t_p1
partition of part_t for values with (modulus 4, remainder 0);
create table part_t_p2
partition of part_t for values with (modulus 4, remainder 1);
create table part_t_p3
partition of part_t for values with (modulus 4, remainder 2);
create table part_t_p4
partition of part_t for values with (modulus 4, remainder 3);
insert into part_t select generate_series(1,100);
我們可以通過(guò)以下查詢返回每行數(shù)據(jù)所在的分區(qū)：
hrdb=> select tableoid::regclass, id
hrdb-> from part_t
hrdb-> order by id
hrdb-> limit 10;
tableoid | id
-----------+----
part_t_p1 | 1
part_t_p3 | 2
part_t_p2 | 3
part_t_p4 | 4
part_t_p2 | 5
part_t_p4 | 6
part_t_p4 | 7
part_t_p2 | 8
part_t_p2 | 9
part_t_p4 | 10
(10 rows)
對(duì)于集合操作 UNION、INTERSECT、EXCEPT 也是如此：
hrdb=> select tableoid::regclass, col from test
hrdb-> union all
hrdb-> select tableoid::regclass, id from part_t where id < 4
hrdb-> order by 2;
tableoid | col
-----------+-----
test | 1
part_t_p1 | 1
test | 2
part_t_p3 | 2
test | 3
part_t_p2 | 3
(6 rows)
ctid
ctid 字段代表了數(shù)據(jù)行在表中的物理位置，也就是行標(biāo)識(shí)（tuple identifier），由一對(duì)數(shù)值組成（塊編號(hào)和行索引）。ctid 類似于 Oracle 中的偽列 ROWID。
ctid 可以用于快速查找表中的數(shù)據(jù)行，也可以用于修復(fù)數(shù)據(jù)損壞。另外，它也可以用于查找并刪除表中的重復(fù)數(shù)據(jù)。例如：
insert into test(col)
values (1),(2),(3);
hrdb=> select ctid, * from test;
ctid | col
-------+-----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 1
(0,5) | 2
(0,6) | 3
(6 rows)
我們?yōu)?test 表插入了 3 條重復(fù)的數(shù)據(jù)。接下來(lái)利用 ctid 刪除重復(fù)的數(shù)據(jù)：
hrdb=> delete from test
hrdb-> where ctid not in
hrdb-> (
hrdb(> select max(ctid)
hrdb(> from test
hrdb(> group by col
hrdb(> );
DELETE 3
需要注意的是，ctid 的值有可能會(huì)改變（例如 VACUUM FULL）；因此，ctid 不適合作為一個(gè)長(zhǎng)期的行標(biāo)識(shí)，應(yīng)該使用主鍵作為行的邏輯標(biāo)識(shí)。
xmin
xmin 代表了該行版本（row version ）的插入事務(wù) ID（XID）。行版本是數(shù)據(jù)行的具體狀態(tài)，每次更新操作都會(huì)為相同的邏輯行創(chuàng)建一個(gè)新的行版本（多版本并發(fā)控制，MVCC）。事務(wù) ID 是一個(gè) 32 bit 數(shù)字。
我們繼續(xù)為 test 表插入幾條數(shù)據(jù)，并查看它們的 xmin：
hrdb=> insert into test(col) values(4);
INSERT 0 1
hrdb=> insert into test(col) values(5);
INSERT 0 1
hrdb=> select xmin,col from test;
xmin | col
------+-----
2852 | 1
2852 | 2
2852 | 3
2854 | 4
2855 | 5
(5 rows)
xmin 字段可以用于查看數(shù)據(jù)行的插入時(shí)間：
hrdb=> select col,
hrdb-> to_char(pg_xact_commit_timestamp(xmin) ,'YYYY-MM-DD HH24:MI:SS') AS insert_time
hrdb-> from test;
col | insert_time
-----+---------------------
1 | 2020-05-28 16:52:08
2 | 2020-05-28 16:52:08
3 | 2020-05-28 16:52:08
4 | 2020-05-28 17:03:33
5 | 2020-05-28 17:03:35
(5 rows)
注意，系統(tǒng)函數(shù) pg_xact_commit_timestamp 需要將配置參數(shù) track_commit_timestamp 設(shè)置為 on 才能使用。
xmax
xmax 字段代表了刪除改行的事務(wù) ID，對(duì)于未刪除的行版本顯示為 0。非零的 xmax 通常意味著刪除事務(wù)還沒有提交，或者刪除操作被回滾。
我們查看一下 test 表中的 xmax：
hrdb=> select txid_current();
txid_current
--------------
2858
(1 row)
hrdb=> select xmax, col from test;
xmax | col
------+-----
0 | 1
0 | 2
0 | 3
0 | 4
0 | 5
(5 rows)
然后打開另一個(gè)會(huì)話，在事務(wù)中修改 test 表中的數(shù)據(jù)：
-- 會(huì)話 2
hrdb=> update test
hrdb-> set col= col*2;
UPDATE 5
回到第一個(gè)會(huì)話，再次查看 xmax：
hrdb=> select xmax, col from test;
xmax | col
------+-----
2858 | 1
2858 | 2
2858 | 3
2858 | 4
2858 | 5
(5 rows)
2858 是第二個(gè)會(huì)話的事務(wù) ID，它是刪除這些行版本的事務(wù)。PostgreSQL 中的 UPDATE 相當(dāng)于 DELETE 加 INSERT。
將第二個(gè)事務(wù)回滾：
-- 會(huì)話 2
hrdb=> rollback;
ROLLBACK
如果再次查詢 test 表中的 xmax，仍然返回 2858。
xmax 還有可能表示當(dāng)前正在占用行鎖的事務(wù) ID，利用 PostgreSQL 擴(kuò)展插件 pageinspect 可以獲取詳細(xì)信息：
create extension pageinspect;
select t.col,
t.xmax
case
when (t_infomask & 128)::boolean then 'LOCK'
when (t_infomask & 1024)::boolean then 'COMMITTED'
when (t_infomask & 2048)::boolean then 'ROLLBACKED'
when (t_infomask & 4096)::boolean then 'MULTI XACT'
end as xmax_info
from test t
left outer join heap_page_items(get_raw_page('test', 0)) hp on (t.ctid = hp.t_ctid)
where hp.t_xmax = t.xmax;
cmin
cmin 代表了插入事務(wù)中的命令標(biāo)識(shí)符（從 0 開始）。命令標(biāo)識(shí)符是一個(gè) 32 bit 數(shù)字。
cmax
cmax 代表了刪除事務(wù)中的命令標(biāo)識(shí)符，或者 0。
我們先查看一下 test 表中的
hrdb=> select cmin, cmax, col from test;
cmin | cmax | col
------+------+-----
0 | 0 | 1
0 | 0 | 2
0 | 0 | 3
0 | 0 | 4
0 | 0 | 5
(5 rows)
然后在事務(wù)中修改數(shù)據(jù)：
hrdb=> begin;
BEGIN
hrdb=> select txid_current();
txid_current
--------------
2859
(1 row)
hrdb=> insert into test(col) values(6);
INSERT 0 1
hrdb=> insert into test(col) values(7);
INSERT 0 1
hrdb=> insert into test(col) values(8);
INSERT 0 1
hrdb=> select cmin, cmax, col from test;
cmin | cmax | col
------+------+-----
0 | 0 | 1
0 | 0 | 2
0 | 0 | 3
0 | 0 | 4
0 | 0 | 5
0 | 0 | 6
1 | 1 | 7
2 | 2 | 8
(8 rows)
然后刪除一條記錄：
hrdb=> delete from test where col=1;
DELETE 1
1
2
此時(shí)，從另一個(gè)會(huì)話中查看：
-- 會(huì)話 2
hrdb=> select cmin, cmax, col from test;
cmin | cmax | col
------+------+-----
3 | 3 | 1
0 | 0 | 2
0 | 0 | 3
0 | 0 | 4
0 | 0 | 5
(5 rows)
oid
如果使用 PostgreSQL 11 或者更早版本，還有一個(gè)隱藏的系統(tǒng)字段：oid。它代表了數(shù)據(jù)行的對(duì)象 ID，只有當(dāng)創(chuàng)建表時(shí)使用了 WITH OIDS 選項(xiàng)或者配置參數(shù) default_with_oids 設(shè)置為 true 時(shí)才會(huì)創(chuàng)建這個(gè)字段。
從 PostgreSQL 12 開始，不再支持 WITH OIDS 選項(xiàng)，oid 只用于系統(tǒng)內(nèi)部。
總結(jié)
PostgreSQL 中的每個(gè)表都包含了 6 個(gè)隱藏的系統(tǒng)字段，可以用于獲取關(guān)于數(shù)據(jù)行的一些內(nèi)部信息。這些字段名稱不能用于創(chuàng)建普通的字段，即使使用雙引號(hào)包含也不可以。
分類導(dǎo)航