Postgresql - 表中系统列 system columns

每个表有由系统隐式定义的多个系统列。这些名称不能用作用户定义列的名称。

 

oid: 行的对象标识符（对象ID）。

tableoid: 表的OID。此列对于从继承层次结构中选择的查询特别有用，因为没有它，很难判断出哪一个单独的表来自哪里。tableoid可以与 pg_class的oid列连接以获得表名。

xmin: 此行版本的插入事务的标识（事务ID）。行版本是行的单独状态；行的每一次更新都为同一逻辑行创建新行版本。

cmin: 插入事务中的命令标识符（从零开始）。

xmax: 删除事务的标识（事务ID），或未删除行版本的零。此列在可见行版本中是非零的。这通常表明删除事务尚未提交，或者尝试删除被回滚。

cmax: 删除事务中的命令标识符，或为零。

ctid: 行版本在其表中的物理位置。注意，虽然CTID可以很快地定位行版本，但是如果它被真空填充更新或移动，行的CTID将改变。因此CTID作为长期行标识符是无用的。应该使用OID，甚至更好的用户定义的序列号来标识逻辑行。

 

通过对表的插入、更新、删除来查看这些系统列的变化。来了解pg的一些简单的原理。

实验环境

CentOS 7 + PG 10.1

 

# 创建表，插入数据

mytest=# create table test0723 (id int primary key, col1 varchar(20) not null, utime timestamp without time zone not null default now() );

CREATE TABLE

mytest=# insert into test0723 values (1,'a');

INSERT 0 1

mytest=# insert into test0723 values (2,'b');

INSERT 0 1

mytest=# insert into test0723 values (3,'c');

INSERT 0 1

mytest=# insert into test0723 values (4,'d');

INSERT 0 1

# 查看系统列

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2055 | 0 | 0 | 0 | (0,1) | 1 | a | 2018-07-23 02:40:20.448984

16713 | 2056 | 0 | 0 | 0 | (0,2) | 2 | b | 2018-07-23 02:40:24.547137

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2058 | 0 | 0 | 0 | (0,4) | 4 | d | 2018-07-23 02:41:09.007699

(4 rows)

 

在更新时，pg并不是在原有基础上修改，而是将原有列删除，再添加新的一行。注意ctid的变化。

# 更新行

mytest=# update test0723 set col1 = 'x', utime = now() where id = 1;

UPDATE 1

# 查看系统列

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2056 | 0 | 0 | 0 | (0,2) | 2 | b | 2018-07-23 02:40:24.547137

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2058 | 0 | 0 | 0 | (0,4) | 4 | d | 2018-07-23 02:41:09.007699

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

(4 rows)

# 删除行

mytest=# delete from test0723 where id = 4;

DELETE 1

# 插入行

mytest=# insert into test0723 values (5,'e');

INSERT 0 1

# 查看系统列

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2056 | 0 | 0 | 0 | (0,2) | 2 | b | 2018-07-23 02:40:24.547137

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

(4 rows)

 

 

************************

通过不同的session看更新。在事务中更新之后，本事务会读取为最新的行，而其他会话在读取时，xmax中会显示该行已经被修改的事务号。

# Session 1

mytest=# begin;

BEGIN

mytest=# update test0723 set col1 = 'y', utime = now() where id = 2;

UPDATE 1

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

16713 | 2062 | 0 | 0 | 0 | (0,7) | 2 | y | 2018-07-23 02:46:58.430202

(4 rows)

 

# Session 2

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2056 | 0 | 2062 | 0 | (0,2) | 2 | b | 2018-07-23 02:40:24.547137

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

(4 rows)

 

# Session 1

mytest=# commit;

COMMIT

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

16713 | 2062 | 0 | 0 | 0 | (0,7) | 2 | y | 2018-07-23 02:46:58.430202

(4 rows)

 

# Session 2

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723 ;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2057 | 0 | 0 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

16713 | 2062 | 0 | 0 | 0 | (0,7) | 2 | y | 2018-07-23 02:46:58.430202

(4 rows)

 

************************

在同一事务中的多个操作。cmin，cmax表示在一个食物中的

# Session 1

mytest=# begin;

BEGIN

mytest=# insert into test0723 values (101,'aa');

INSERT 0 1

mytest=# insert into test0723 values (102,'bb');

INSERT 0 1

mytest=# update test0723 set col1 = 'ff' where id = 6;

UPDATE 1

mytest=# delete from test0723 where id = 7;

DELETE 1

mytest=# insert into test0723 values (103,'cc');

INSERT 0 1

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+--------+-----+------+----------------------------

16713 | 2057 | 0 | 2066 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

16713 | 2062 | 0 | 0 | 0 | (0,7) | 2 | y | 2018-07-23 02:46:58.430202

16713 | 2067 | 0 | 0 | 0 | (0,13) | 101 | aa | 2018-07-23 04:31:56.542984

16713 | 2067 | 1 | 0 | 1 | (0,14) | 102 | bb | 2018-07-23 04:31:56.542984

16713 | 2067 | 2 | 0 | 2 | (0,15) | 6 | ff | 2018-07-23 04:05:22.181624

16713 | 2067 | 4 | 0 | 4 | (0,16) | 103 | cc | 2018-07-23 04:31:56.542984

(8 rows)

 

# Session 2

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+-------+----+------+----------------------------

16713 | 2057 | 0 | 2066 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

16713 | 2062 | 0 | 0 | 0 | (0,7) | 2 | y | 2018-07-23 02:46:58.430202

16713 | 2063 | 2 | 2067 | 2 | (0,8) | 6 | f | 2018-07-23 04:05:22.181624

16713 | 2063 | 3 | 2067 | 3 | (0,9) | 7 | g | 2018-07-23 04:05:22.181624

(6 rows)

# Session 1

mytest=# commit;

COMMIT

# Session 2

mytest=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, col1, utime from test0723;

tableoid | xmin | cmin | xmax | cmax | ctid | id | col1 | utime

----------+------+------+------+------+--------+-----+------+----------------------------

16713 | 2057 | 0 | 2066 | 0 | (0,3) | 3 | c | 2018-07-23 02:41:04.521412

16713 | 2059 | 0 | 0 | 0 | (0,5) | 1 | x | 2018-07-23 02:45:05.250456

16713 | 2061 | 0 | 0 | 0 | (0,6) | 5 | e | 2018-07-23 02:46:11.396763

16713 | 2062 | 0 | 0 | 0 | (0,7) | 2 | y | 2018-07-23 02:46:58.430202

16713 | 2067 | 0 | 0 | 0 | (0,13) | 101 | aa | 2018-07-23 04:31:56.542984

16713 | 2067 | 1 | 0 | 1 | (0,14) | 102 | bb | 2018-07-23 04:31:56.542984

16713 | 2067 | 2 | 0 | 2 | (0,15) | 6 | ff | 2018-07-23 04:05:22.181624

16713 | 2067 | 4 | 0 | 4 | (0,16) | 103 | cc | 2018-07-23 04:31:56.542984

(8 rows)