对PostgreSQL cmin和cmax的理解

对PostgreSQL cmin和cmax的理解

看例子：

开两个终端来对比：

在终端A:

[pgsql@localhost bin]$ ./psql

psql (9.1.)

Type "help" for help.

pgsql=# begin;

BEGIN

pgsql=# select xmin,xmax,cmin,cmax,* from tab01;

 xmin | xmax | cmin | cmax |    id     | cd

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

  |     |     |     |          |

  |     |     |     |  |

( rows)

pgsql=# insert into tab01 values(,'');

INSERT

pgsql=# insert into tab01 values(,'');

INSERT

pgsql=# select xmin,xmax,cmin,cmax,* from tab01;

 xmin | xmax | cmin | cmax |    id     | cd

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

  |     |     |     |          |

  |     |     |     |  |

  |     |     |     |          |

  |     |     |     |          |

( rows)

pgsql=# 

此时的终端B:

[pgsql@localhost bin]$ ./psql

psql (9.1.)

Type "help" for help.

pgsql=# select xmin,xmax,cmin,cmax, * from tab01;

 xmin | xmax | cmin | cmax |    id     | cd

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

  |     |     |     |          |

  |     |     |     |  |

( rows)

pgsql=# 

然后再在终端A进行提交：

pgsql=# commit;

COMMIT

pgsql=# select xmin,xmax,cmin,cmax,* from tab01;

 xmin | xmax | cmin | cmax |    id     | cd

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

  |     |     |     |          |

  |     |     |     |  |

  |     |     |     |          |

  |     |     |     |          |

( rows)

pgsql=# 

此时，再在终端B进行观察：

pgsql=# select xmin,xmax,cmin,cmax, * from tab01;

 xmin | xmax | cmin | cmax |    id     | cd

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

  |     |     |     |          |

  |     |     |     |  |

  |     |     |     |          |

  |     |     |     |          |

( rows)

pgsql=# 

继续研究cmin是咋回事：

pgsql=# begin;

BEGIN

pgsql=# insert into tab01(id,cd) values(generate_series(,),'xx');

INSERT

pgsql=# select xmin,xmax,cmin,cmax,* from tab01;

 xmin | xmax | cmin | cmax |    id     | cd

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

  |     |     |     |          |

  |     |     |     |  |

  |     |     |     |          |

  |     |     |     |          |

  |     |     |     |          | xx

  |     |     |     |          | xx

( rows)

pgsql=# 

可以说cmin可理解为一个事务里，执行了几次sql命令的顺序。

那么cmax呢？在前面的基础上继续执行，居然没有看到区别：

pgsql=# update tab01 set id= where cd = '';

UPDATE

pgsql=# select xmin,xmax,cmin,cmax,* from tab01;

 xmin | xmax | cmin | cmax | id | cd

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

  |     |     |     |   |

  |     |     |     |   |

  |     |     |     |   |

  |     |     |     |   | xx

  |     |     |     |   | xx

  |     |     |     |   |

( rows)

pgsql=# commit;

COMMIT

pgsql=# select xmin,xmax,cmin,cmax,* from tab01;

 xmin | xmax | cmin | cmax | id | cd

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

  |     |     |     |   |

  |     |     |     |   |

  |     |     |     |   |

  |     |     |     |   | xx

  |     |     |     |   | xx

  |     |     |     |   |

( 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 = ;            /* keep compiler quiet */

            break;

    }

    return result;

}