对PostgreSQL cmin和cmax的理解
看例子：
开两个终端来对比：
在终端A:
[pgsql@localhost bin]$ ./psql psql (9.1.2) Type "help" for help. pgsql=# begin; BEGIN pgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | 0 | 999888777 | 2 (2 rows) pgsql=# insert into tab01 values(3,'3'); INSERT 0 1 pgsql=# insert into tab01 values(4,'4'); INSERT 0 1 pgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | 0 | 999888777 | 2 1885 | 0 | 0 | 0 | 3 | 3 1885 | 0 | 1 | 1 | 4 | 4 (4 rows) pgsql=#
此时的终端B:
[pgsql@localhost bin]$ ./psql psql (9.1.2) Type "help" for help. pgsql=# select xmin,xmax,cmin,cmax, * from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | 0 | 999888777 | 2 (2 rows) pgsql=#
然后再在终端A进行提交：
pgsql=# commit; COMMIT pgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | 0 | 999888777 | 2 1885 | 0 | 0 | 0 | 3 | 3 1885 | 0 | 1 | 1 | 4 | 4 (4 rows) pgsql=#
此时，再在终端B进行观察：
pgsql=# select xmin,xmax,cmin,cmax, * from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | 0 | 999888777 | 2 1885 | 0 | 0 | 0 | 3 | 3 1885 | 0 | 1 | 1 | 4 | 4 (4 rows) pgsql=#
继续研究cmin是咋回事：
pgsql=# begin; BEGIN pgsql=# insert into tab01(id,cd) values(generate_series(5,6),'xx'); INSERT 0 2 pgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | 0 | 999888777 | 2 1885 | 0 | 0 | 0 | 3 | 3 1885 | 0 | 1 | 1 | 4 | 4 1886 | 0 | 0 | 0 | 5 | xx 1886 | 0 | 0 | 0 | 6 | xx (6 rows) pgsql=#
可以说cmin可理解为一个事务里，执行了几次sql命令的顺序。
那么cmax呢？在前面的基础上继续执行，居然没有看到区别：
pgsql=# update tab01 set id=2 where cd = '2'; UPDATE 1 pgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+----+---- 1878 | 0 | 0 | 0 | 1 | 1 1885 | 0 | 0 | 0 | 3 | 3 1885 | 0 | 1 | 1 | 4 | 4 1886 | 0 | 0 | 0 | 5 | xx 1886 | 0 | 0 | 0 | 6 | xx 1886 | 0 | 1 | 1 | 2 | 2 (6 rows) pgsql=# commit; COMMIT pgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+----+---- 1878 | 0 | 0 | 0 | 1 | 1 1885 | 0 | 0 | 0 | 3 | 3 1885 | 0 | 1 | 1 | 4 | 4 1886 | 0 | 0 | 0 | 5 | xx 1886 | 0 | 0 | 0 | 6 | xx 1886 | 0 | 1 | 1 | 2 | 2 (6 rows) pgsql=#
经过反复折腾，终于发现，其实 cmin和 cmax就是一个东西：
看源代码：
/* ---------------- * heap_getsysattr * * Fetch the value of a system attribute for a tuple. * * This is a support routine for the heap_getattr macro. The macro * has already determined that the attnum refers to a system attribute. * ---------------- */ Datum heap_getsysattr(HeapTuple tup, int attnum, TupleDesc tupleDesc, bool *isnull) { Datum result; Assert(tup); /* Currently, no sys attribute ever reads as NULL. */ *isnull = false; switch (attnum) { case SelfItemPointerAttributeNumber: /* pass-by-reference datatype */ result = PointerGetDatum(&(tup->t_self)); break; case ObjectIdAttributeNumber: result = ObjectIdGetDatum(HeapTupleGetOid(tup)); break; case MinTransactionIdAttributeNumber: result = TransactionIdGetDatum(HeapTupleHeaderGetXmin(tup->t_data)); break; case MaxTransactionIdAttributeNumber: result = TransactionIdGetDatum(HeapTupleHeaderGetXmax(tup->t_data)); break; case MinCommandIdAttributeNumber: case MaxCommandIdAttributeNumber: /* * cmin and cmax are now both aliases for the same field, which * can in fact also be a combo command id. XXX perhaps we should * return the "real" cmin or cmax if possible, that is if we are * inside the originating transaction? */ result = CommandIdGetDatum(HeapTupleHeaderGetRawCommandId(tup->t_data)); break; case TableOidAttributeNumber: result = ObjectIdGetDatum(tup->t_tableOid); break; default: elog(ERROR, "invalid attnum: %d", attnum); result = 0; /* keep compiler quiet */ break; } return result; }