PostgreSQL表的系统字段

PostgreSQL中表都包含一些系统字段，这些字段默认通过select *是无法查看的，同样用户自己创建的字段也不能和这些系统字段重名，这些字段分别是：

• oid：行对象标识符
• xmin：插入该行版本的事务ID
• xmax：删除此行时的事务ID，对于一个第一次插入数据的行，这个字段的值是0
• cmin：事务内部插入类操作的命令ID，这个值从0开始
• cmax：事务内部删除类操作的命令ID，如果不是删除操作，则这个值为0
• ctid：一个行版本在它所处的表内的物理位置

下面详细介绍下这些字段的用法：
oid：
PostgreSQL在内部使用oid作为各种系统表的主键，默认创建表时不会自动创建oid这个字段，可以通过加上with oids参数来创建。

postgres=# create table t1(id int ,info text) with oids;
CREATE TABLE
postgres=# insert into t1 select generate_series(1,2),md5(random()::text);
INSERT 0 2
postgres=# select oid,* from t1;
  oid   | id |               info               
--------+----+----------------------------------
 168543 |  1 | 35bbfc411e6f966e90ce0519c3e07d61
 168544 |  2 | 0839351fd30984fa44dd1bc9f30a8a61
(2 rows)

可以发现oid并不是从0开始分配的，因为oid的分配是全局的。需要注意的是在最新的pg12版本中这种写法已经无法使用了。

bill=# create table t1(id int ,info text) with oids;
psql: ERROR:  syntax error at or near "oids"
LINE 1: create table t1(id int ,info text) with oids;

pg12中将oid作为一种数据类型来使用，因此我们可以这样创建：

bill=# create table t1(oid oid,id int,info text);
CREATE TABLE

ctid:
ctid有点类似oracle中的rowid，但是不同的是每次vacuum表之后，ctid会变化。

bill=# select ctid,* from t1;
 ctid  | id |               info               
-------+----+----------------------------------
 (0,1) |  1 | adf5b3c208fd802cc29ee06e7d9d4e12
 (0,2) |  2 | 07da948d9ace8e67c94fe7f2030649f3
 (0,3) |  3 | b4cd889c51684ae3e9c55955bfdfa8c8
 (0,4) |  4 | 46fc9cd8e44490dd1444271ea0d065c3
 (0,5) |  5 | a5754f2cadbfb9e3ed1770f445bab87f
(5 rows)

citd的数据类型是tid类型，可以发现ctid由两个数字组成，其中第一个物理块号，第二个表示在数据块中的行号。

xmin、xmax：
xmin、xmax的用法大致如下：
新插入一行时：xmin为当前的事务ID，xmax为0
更新一行时：被更新的行的xmin为当前的事务ID，xmax为0
删除一行使：把被删除行的xmax改为当前事务ID
插入：

bill=# select xmin,xmax,* from t1;
   xmin   | xmax | id |               info               
----------+------+----+----------------------------------
 34056747 |    0 |  1 | adf5b3c208fd802cc29ee06e7d9d4e12
 34056747 |    0 |  2 | 07da948d9ace8e67c94fe7f2030649f3
 34056747 |    0 |  3 | b4cd889c51684ae3e9c55955bfdfa8c8
 34056747 |    0 |  4 | 46fc9cd8e44490dd1444271ea0d065c3
 34056747 |    0 |  5 | a5754f2cadbfb9e3ed1770f445bab87f
(5 rows)

更新：

bill=# update t1 set info ='aaa' where id =1;
UPDATE 1
bill=# select xmin,xmax,* from t1;           
   xmin   | xmax | id |               info               
----------+------+----+----------------------------------
 34056747 |    0 |  2 | 07da948d9ace8e67c94fe7f2030649f3
 34056747 |    0 |  3 | b4cd889c51684ae3e9c55955bfdfa8c8
 34056747 |    0 |  4 | 46fc9cd8e44490dd1444271ea0d065c3
 34056747 |    0 |  5 | a5754f2cadbfb9e3ed1770f445bab87f
 34056748 |    0 |  1 | aaa
(5 rows)

删除：
需要注意的是删除操作即使rollback，xmax依然不会变回0

bill=# delete from t1 where id=2;
DELETE 1
bill=# select xmin,xmax,* from t1;
   xmin   |   xmax   | id |               info               
----------+----------+----+----------------------------------
 34056747 | 34056749 |  2 | 07da948d9ace8e67c94fe7f2030649f3
 34056747 |        0 |  3 | b4cd889c51684ae3e9c55955bfdfa8c8
 34056747 |        0 |  4 | 46fc9cd8e44490dd1444271ea0d065c3
 34056747 |        0 |  5 | a5754f2cadbfb9e3ed1770f445bab87f
 34056748 |        0 |  1 | aaa
(5 rows)

cmin、cmax：
cmin、cmax这两个的概念基本是一个意思，表示的是事务中的命令号。例如在一个事务中有多条insert语句，cmin和cmax就是用来标记数据行是事务中第几个insert语句插入的。例如：

bill=# begin;
BEGIN
bill=# insert into t1 values(1,'test1');
INSERT 0 1
bill=# insert into t1 values(2,'test2');
INSERT 0 1
bill=# insert into t1 values(3,'test3');
INSERT 0 1
bill=# select cmin,cmax,* from t1;
 cmin | cmax | id | info  
------+------+----+-------
    0 |    0 |  1 | test1
    1 |    1 |  2 | test2
    2 |    2 |  3 | test3
(3 rows)

如果使用批量插入的话是什么情况呢？

bill=# insert into t1 values(4,'test4'),(5,'test5');
INSERT 0 2
bill=# select cmin,cmax,* from t1;                  
 cmin | cmax | id | info  
------+------+----+-------
    0 |    0 |  1 | test1
    1 |    1 |  2 | test2
    2 |    2 |  3 | test3
    3 |    3 |  4 | test4
    3 |    3 |  5 | test5
(5 rows)

可以发现id=4和id=5两条记录的cmin、cmax是一样的，说明这两条记录是由同一个语句插入的。