pg中的系统列ctid

jiawang

pg中每个表都有几个系统隐藏列：tableoid， xmin， xmax，cmin，cmax，ctid。其中tableoid表示表的oid，cmin、cmax、xmin和xmax是mvcc的实现有关，今天主要来看下ctid。

ctid:
ctid表示行版本在表中的物理位置。它属于对象标识符类型（oid，Object Identifier Types），是一种行标识符，它的数据使用的元组标识符（tid，tuple identifier）。
元组ID是一对（块号，块内的元组索引），用于标识当前行的物理位置。


postgres=# select tableoid, xmin, xmax,cmin,cmax,ctid from tb1;
tableoid | xmin | xmax | cmin | cmax | ctid
----------+------+------+------+------+-------
    16384 |  487 |  550 |    0 |    0 | (0,3)
    16384 |  487 |    0 |    0 |    0 | (0,4)
(2 行记录)



如果想要根据ctid查询，我们可以使用单引号括起来的类型值格式进行查询.
postgres=# select tableoid, xmin, xmax,cmin,cmax,ctid from tb1 where ctid='(0,3)';
tableoid | xmin | xmax | cmin | cmax | ctid
----------+------+------+------+------+-------
    16384 |  487 |  550 |    0 |    0 | (0,3)
(1 行记录)

这一点还是比较有用的，有时候我们可能会遇到页面损坏的异常，报错信息可能如下：
ERROR:  invalid page in block 1877 of relation base/16402/45678


其中block 1877，就是对应ctid的块号。在主从环境下，当主库发生页损坏，我们在从库上执行读取操作通ctid过滤把损坏的数据找回来。还可以根据块号，定位具体损坏的数据文件等等。


此外，我们知道，数据文件的块的大小，默认是8K，我们还可以根据表数据包含的块数，计算表占的存储大小（块数*8k）。


借助系统视图pg_class，其中relpages,reltuples分别代表块数，记录数。但是这个值不一定是准确的，在查看之前，可以先执行analyze tablename
命令。


postgres=# create table t1 (id int,c_name varchar(100));
CREATE TABLE
postgres=# insert into t1 select generate_series(1,100000),'zsan';
INSERT 0 100000
postgres=# select relpages,reltuples from pg_class where relname = 't1';
relpages | reltuples
----------+-----------
        0 |         0
(1 行记录)


postgres=# analyze t1;
ANALYZE
postgres=# select relpages,reltuples from pg_class where relname = 't1';
relpages | reltuples
----------+-----------
      541 |    100000
(1 行记录)


可以看到表中包含541个数据块，我们再看下表的大小是不是8*541=4328


postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
4328 kB
(1 行记录)




postgres=# select 541*8;
?column?
----------
     4328
(1 行记录)


总结
ctid是pg表的系统隐藏列，它表示行版本在表中的物理位置。它属于对象标识符类型（oid，Object Identifier Types），是一种行标识符，它的数据使用的元组标识符（tid，tuple identifier）。元组ID是一对（块号，块内的元组索引），用于标识当前行的物理位置。我们可以根据ctid查询表数据，在数据页损坏的场景，可以帮助定位具体损坏的数据文件以及恢复数据。