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

文章目录

        tableoid
        ctid
        xmin
        xmax
        cmin
        cmax
        oid
        总结

今天我们来谈谈 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_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)


查询结果显示，表 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.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 的另一个用途就是在涉及分区表查询或者 UNION 操作时标识数据行所在的具体表。例如存在以下分区表：

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

   

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

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)



对于集合操作 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 字段代表了数据行在表中的物理位置，也就是行标识（tuple identifier），由一对数值组成（块编号和行索引）。ctid 类似于 Oracle 中的伪列 ROWID。

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

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

更多请见：http://www.mark-to-win.com/tutorial/51616.html