Thread: What is the difference between cmin and cmax
Hello:
I have question for cmin and cmax.
It is said:
cmin is: The command identifier (starting at zero) within the inserting transaction.
cmax is: The command identifier within the deleting transaction, or zero.
http://www.postgresql.org/docs/9.1/static/ddl-system-columns.html
But I wonder what is the difference between cmin and cmax ?
Because during my test, cmin and cmax changed together and be the same value:
At first I have two records.
In my terminal A I did:
[postgres@server bin]$ ./psql
psql (9.1.2)
Type "help" for help.
postgres=# select version();
version
--------------------------------------------------------------------------------
-------------------------------
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080
704 (Red Hat 4.1.2-52), 64-bit
(1 row)
postgres=# begin;
BEGIN
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
(2 rows)
postgres=# insert into tab01 values(3,'3');
INSERT 0 1
postgres=# insert into tab01 values(4,'4');
INSERT 0 1
postgres=# insert into tab01 values(5,'5');
INSERT 0 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 1 | 1 | 4 | 4
1740 | 0 | 2 | 2 | 5 | 5
(5 rows)
postgres=# update tab01 set id=50 where cd = '5';
UPDATE 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 1 | 1 | 4 | 4
1740 | 0 | 3 | 3 | 50 | 5
(5 rows)
postgres=# delete from tab01 where id=4;
DELETE 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 3 | 3 | 50 | 5
(4 rows)
postgres=# delete from tab01 where id=2;
DELETE 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 3 | 3 | 50 | 5
(3 rows)
postgres=#
In terminal B, I did:
[postgres@server bin]$ ./psql
psql (9.1.2)
Type "help" for help.
postgres=# begin;
BEGIN
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 1740 | 5 | 5 | 2 | 2
(2 rows)
postgres=#
Thanks!
Hello:
I have question for cmin and cmax.
It is said:
cmin is: The command identifier (starting at zero) within the inserting transaction.
cmax is: The command identifier within the deleting transaction, or zero.
http://www.postgresql.org/docs/9.1/static/ddl-system-columns.html
But I wonder what is the difference between cmin and cmax ?
Because during my test, cmin and cmax changed together and be the same value:
At first I have two records.
In my terminal A I did:
[postgres@server bin]$ ./psql
psql (9.1.2)
Type "help" for help.
postgres=# select version();
version
--------------------------------------------------------------------------------
-------------------------------
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080
704 (Red Hat 4.1.2-52), 64-bit
(1 row)
postgres=# begin;
BEGIN
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
(2 rows)
postgres=# insert into tab01 values(3,'3');
INSERT 0 1
postgres=# insert into tab01 values(4,'4');
INSERT 0 1
postgres=# insert into tab01 values(5,'5');
INSERT 0 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 1 | 1 | 4 | 4
1740 | 0 | 2 | 2 | 5 | 5
(5 rows)
postgres=# update tab01 set id=50 where cd = '5';
UPDATE 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 1 | 1 | 4 | 4
1740 | 0 | 3 | 3 | 50 | 5
(5 rows)
postgres=# delete from tab01 where id=4;
DELETE 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 0 | 0 | 0 | 2 | 2
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 3 | 3 | 50 | 5
(4 rows)
postgres=# delete from tab01 where id=2;
DELETE 1
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1740 | 0 | 0 | 0 | 3 | 3
1740 | 0 | 3 | 3 | 50 | 5
(3 rows)
postgres=#
In terminal B, I did:
[postgres@server bin]$ ./psql
psql (9.1.2)
Type "help" for help.
postgres=# begin;
BEGIN
postgres=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1738 | 0 | 0 | 0 | 1 | 1
1739 | 1740 | 5 | 5 | 2 | 2
(2 rows)
postgres=#
Thanks!
On Tue, Jul 2, 2013 at 5:19 AM, 高健 <luckyjackgao@gmail.com> wrote: > Hello: > I looked into the source code, and I think I now understand it: > cmin and cmax are same! The documentation is too old now. Yes, you figured it out. For short: cmin and cmax are overlapped fields and are used within the same transaction to identify the command that changed a tuple, so that in-transaction commands can be ordered and, therefore, tuple visibility can be calculated. Luca