What is the difference between cmin and cmax

Started by 高健almost 13 years ago3 messagesgeneral

Jump to latest

高健

luckyjackgao@gmail.com

almost 13 years ago

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

1738 | 0 | 0 | 0 | 1 | 1

1739 | 1740 | 5 | 5 | 2 | 2

(2 rows)

postgres=#

Thanks!

高健

luckyjackgao@gmail.com

almost 13 years ago

In reply to: 高健 (#1)

Re: What is the difference between cmin and cmax

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.

I made another test:
In terminal A:

pgsql=# begin;
BEGIN
pgsql=# select * from tab01;
id | cd
----+----
(0 rows)

pgsql=# insert into tab01 values(1,'1'),(2,'2'),(3,'3');
INSERT 0 3
pgsql=# insert into tab01 values(4,'4'),(5,'5'),(6,'6');
INSERT 0 3
pgsql=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1897 | 0 | 0 | 0 | 1 | 1
1897 | 0 | 0 | 0 | 2 | 2
1897 | 0 | 0 | 0 | 3 | 3
1897 | 0 | 1 | 1 | 4 | 4
1897 | 0 | 1 | 1 | 5 | 5
1897 | 0 | 1 | 1 | 6 | 6
(6 rows)

pgsql=# commit;

Then I begin to delete record:
pgsql=# begin;
BEGIN
pgsql=# delete from tab01 where id=1 or id=2;
DELETE 2
pgsql=# delete from tab01 where id=3;
DELETE 1
pgsql=# delete from tab01 where id=4;
DELETE 1
pgsql=# delete from tab01 where id=5;
DELETE 1
pgsql=#

But I have not commit my deleting action.
Then in terminal B, I can see:
pgsql=# select xmin,xmax,cmin,cmax,* from tab01;
xmin | xmax | cmin | cmax | id | cd
------+------+------+------+----+----
1897 | 1898 | 0 | 0 | 1 | 1
1897 | 1898 | 0 | 0 | 2 | 2
1897 | 1898 | 1 | 1 | 3 | 3
1897 | 1898 | 2 | 2 | 4 | 4
1897 | 1898 | 3 | 3 | 5 | 5
1897 | 0 | 1 | 1 | 6 | 6
(6 rows)

pgsql=#

---------------
In fact , in the source code of PG,I can find it---heap_getsysattr function
in heaptuple.c,here is it:
--------code begin----

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;

--------code end-------

2013/7/2 高健 <luckyjackgao@gmail.com>

Show quoted text

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!

Luca Ferrari

fluca1978@infinito.it

almost 13 years ago

In reply to: 高健 (#2)

Re: What is the difference between cmin and cmax

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general