PostgreSQL 中的系统字段：tableoid、xmin、xmax、cmin、cmax、ctid

概述PostgreSQL 中的每个表都包含了 6 个隐藏的系统字段：tableoid、xmin、xmax、cmin、cmax、ctid，这些字段可以用于获取关于数据行的一些内部信息。

文章目录 tableoidctidxminxmaxcmincmaxoid总结

大家好！我是只谈技术不剪发的 Tony 老师。今天我们来谈谈 Postgresql 数据表中几个隐藏的系统字段和它们的作用。

在 Postgresql 中，当我们创建一个数据表时，数据库会隐式增加几个系统字段。这些字段由系统进行维护，用户一般不会感知它们的存在。例如，以下语句创建了一个简单的表：

create table test(col integer);insert into test(col)values (1),(2),(3);

从定义上来看，表 test 中只有一个字段；但是当我们查询数据字典表 pg_attribute 时，结果却不是如此：

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_attributehrdb-> 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)

查询结果显示，表 test 中一共包含 7 个字段。Postgresql 为我们增加了 6 个额外的系统字段，它们的 attnum 属性都是负数。

下面让我们分别看看这些系统字段的作用。

tableoID

tableoID 字段代表了数据所在表的对象 ID（OID），也就是数据字典表 pg_class 中与该表信息相关的数据行。

hrdb=> select oID, relname from pg_class where relname = 'test';  oID  | relname -------+--------- 90277 | test(1 row)hrdb=> select t.tableoID, t.col, c.relnamehrdb-> from test thrdb-> join pg_class c on (c.oID = t.tableoID); tableoID | col | relname ----------+-----+---------    90277 |   1 | test    90277 |   2 | test    90277 |   3 | test(3 rows)

tableoID 的另一个用途就是在涉及分区表查询或者 UNION *** 作时标识数据行所在的具体表。例如存在以下分区表：

create table part_t(ID integer) partition by hash (ID);create table part_t_p1partition of part_t for values with (modulus 4, remainder 0);create table part_t_p2partition of part_t for values with (modulus 4, remainder 1);create table part_t_p3partition of part_t for values with (modulus 4, remainder 2);create table part_t_p4partition of part_t for values with (modulus 4, remainder 3);insert into part_t select generate_serIEs(1,100);

我们可以通过以下查询返回每行数据所在的分区：

hrdb=> select tableoID::regclass, IDhrdb-> from part_thrdb-> order by IDhrdb-> 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)

对于集合 *** 作 UNION、INTERSECT、EXCEPT 也是如此：

hrdb=> select tableoID::regclass, col from testhrdb-> union allhrdb-> select tableoID::regclass, ID from part_t where ID < 4hrdb-> 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 字段代表了数据行在表中的物理位置，也就是行标识（tuple IDentifIEr），由一对数值组成（块编号和行索引）。ctID 类似于 Oracle 中的伪列 ROWID。

ctID 可以用于快速查找表中的数据行，也可以用于修复数据损坏。另外，它也可以用于查找并删除表中的重复数据。例如：

insert into test(col)values (1),(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)

我们为 test 表插入了 3 条重复的数据。接下来利用 ctID 删除重复的数据：

hrdb=> delete from test hrdb-> where ctID not inhrdb-> (hrdb(>   select max(ctID) hrdb(>   from test hrdb(>   group by colhrdb(> );DELETE 3

需要注意的是，ctID 的值有可能会改变（例如 VACUUM FulL）；因此，ctID 不适合作为一个长期的行标识，应该使用主键作为行的逻辑标识。

xmin

xmin 代表了该行版本（row version ）的插入事务 ID（XID）。行版本是数据行的具体状态，每次更新 *** 作都会为相同的逻辑行创建一个新的行版本（多版本并发控制，MVCC）。事务 ID 是一个 32 bit 数字。

我们继续为 test 表插入几条数据，并查看它们的 xmin：

hrdb=> insert into test(col) values(4);INSERT 0 1hrdb=> insert into test(col) values(5);INSERT 0 1hrdb=> select xmin,col from test; xmin | col ------+----- 2852 |   1 2852 |   2 2852 |   3 2854 |   4 2855 |   5(5 rows)

xmin 字段可以用于查看数据行的插入时间：

hrdb=> select col,hrdb->        to_char(pg_xact_commit_timestamp(xmin) ,'YYYY-MM-DD HH24:MI:SS') AS insert_timehrdb-> 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)

注意，系统函数 pg_xact_commit_timestamp 需要将配置参数 track_commit_timestamp 设置为 on 才能使用。

xmax

xmax 字段代表了删除改行的事务 ID，对于未删除的行版本显示为 0。非零的 xmax 通常意味着删除事务还没有提交，或者删除 *** 作被回滚。

我们查看一下 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)

然后打开另一个会话，在事务中修改 test 表中的数据：

-- 会话 2hrdb=> update testhrdb-> set col= col*2;UPDATE 5

回到第一个会话，再次查看 xmax：

hrdb=> select xmax, col from test; xmax | col ------+----- 2858 |   1 2858 |   2 2858 |   3 2858 |   4 2858 |   5(5 rows)

2858 是第二个会话的事务 ID，它是删除这些行版本的事务。Postgresql 中的 UPDATE 相当于 DELETE 加 INSERT。

将第二个事务回滚：

-- 会话 2hrdb=> rollback;RolLBACK

如果再次查询 test 表中的 xmax，仍然返回 2858。

xmax 还有可能表示当前正在占用行锁的事务 ID，利用 Postgresql 扩展插件 pageinspect 可以获取详细信息：

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_infofrom 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 代表了插入事务中的命令标识符（从 0 开始）。命令标识符是一个 32 bit 数字。

cmax

cmax 代表了删除事务中的命令标识符，或者 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)

然后在事务中修改数据：

hrdb=> begin;BEGINhrdb=> select txID_current(); txID_current --------------         2859(1 row)hrdb=> insert into test(col) values(6);INSERT 0 1hrdb=> insert into test(col) values(7);INSERT 0 1hrdb=> insert into test(col) values(8);INSERT 0 1hrdb=> select cmin, 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

此时，从另一个会话中查看：

-- 会话 2hrdb=> select cmin, 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 或者更早版本，还有一个隐藏的系统字段：oid。它代表了数据行的对象 ID，只有当创建表时使用了 WITH OIDS 选项或者配置参数 default_with_oIDs 设置为 true 时才会创建这个字段。

从 Postgresql 12 开始，不再支持 WITH OIDS 选项，oID 只用于系统内部。

总结

Postgresql 中的每个表都包含了 6 个隐藏的系统字段，可以用于获取关于数据行的一些内部信息。这些字段名称不能用于创建普通的字段，即使使用双引号包含也不可以。

如果你点击了收藏⭐，请不要忘了关注❤️、评论 总结

以上是内存溢出为你收集整理的PostgreSQL 中的系统字段：tableoid、xmin、xmax、cmin、cmax、ctid全部内容，希望文章能够帮你解决PostgreSQL 中的系统字段：tableoid、xmin、xmax、cmin、cmax、ctid所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。