postgresql/lightdb CommandCounterIncrement()函数的作用

　　CommandCounterIncrement的作用是使当前事务中前面语句的修改对本语句可见，相当于oracle中的当前读概念（current read，只不过oracle区分，pg不区分）。事务中每执行一个语句后，对后续语句都会直接可见。如下：

zjh@postgres=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
zjh@postgres=*# select * from t;
 id | v  
----+----
  1 | v1
(1 row)

zjh@postgres=*# insert into t values(2,'v2');
INSERT 0 1
zjh@postgres=*# select * from t;
 id | v  
----+----
  1 | v1
  2 | v2
(2 rows)

zjh@postgres=*# rollback;
ROLLBACK

该函数的作用和cid没有关系，cid是标记当前行是被事务中的第几个语句修改。存储在元组头src/include/access/htup_details.h中，如下：

typedef struct HeapTupleFields
{
    TransactionId t_xmin;        /* inserting xact ID */
    TransactionId t_xmax;        /* deleting or locking xact ID */

    union
    {
        CommandId    t_cid;        /* inserting or deleting command ID, or both */
        TransactionId t_xvac;    /* old-style VACUUM FULL xact ID */
    }            t_field3;
} HeapTupleFields;

可通过pageinspect查询每行记录的元组头，如下：

zjh@postgres=# SELECT * FROM heap_page_items(get_raw_page('pg_class', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff |                  t_bits                  | t_oid |                                
                                                                                                                                                        t_data                                 
                                                                                                                                                       
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+------------------------------------------+-------+--------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
  1 |     47 |        2 |      0 |        |        |          |        |             |            |        |                                          |       | 
  2 |   7584 |        1 |    172 |    488 |    488 |        0 | (0,5)  |       16417 |       1313 |     32 | 1111111111111111111111111111110000000000 |       | \x004000007461626c655f666f725f7
66163756d000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009808000002400000000000000a0000000200000000400000000000000000000000000000000000000000
00000000707202000000000000000001640000000000e801000001000000
  3 |   7408 |        1 |    172 |    488 |    493 |        6 | (0,13) |          33 |       1281 |     32 | 1111111111111111111111111111110000000000 |       | \x0340000070675f746f6173745f313
633383400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006300000004400000000000000a0000000200000003400000000000000000000000000000000000000000
000001007074030000000000000000016e0000000000e801000001000000
  4 |   7232 |        1 |    172 |    488 |    493 |        6 | (0,15) |          33 |       1281 |     32 | 1111111111111111111111111111110000000000 |       | \x0540000070675f746f6173745f313
63338345f696e64657800000000000000000000000000000000000000000000000000000000000000000000000000000000000000006300000000000000000000000a0000009301000005400000000000000100000000000000000000000000
000000007069020000000000000000016e00000000000000000000000000
  5 |   7056 |        1 |    172 |    488 |    493 |        6 | (0,11) |       32801 |       9473 |     32 | 1111111111111111111111111111110000000000 |       | \x004000007461626c655f666f725f7
66163756d000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009808000002400000000000000a0000000200000000400000000000000b0000000000fa440b0000000340
00000000707202000000000000000001640000000000e801000001000000

zjh@postgres=# SELECT * FROM heap_page_items(get_raw_page('t', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |      t_data      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------
  1 |   8160 |        1 |     31 |    548 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x01000000077631
  2 |   8128 |        1 |     31 |    549 |      0 |        0 | (0,2)  |           2 |       2562 |     24 |        |       | \x02000000077632
  3 |   8096 |        1 |     31 |    550 |      0 |        0 | (0,3)  |           2 |       2562 |     24 |        |       | \x02000000077632
(3 rows)

https://www.postgresql.org/message-id/flat/200011041823.NAA27229%40candle.pha.pa.us#2b278d5117873db459aa8c3e3a6e9472

如果内核相关部分没有调用CommandCounterIncrement()函数，就会发生当前事务之前的修改对随后的查询不可见的情况，这是不符合sql标准要求的。https://postgrespro.com/list/id/[email protected]