PostgreSQL14在做vacuum時候的邏輯判斷是否經過HEAPTUPLE_RECENTLY_DEAD

哎呀我的天吶發表於2022-03-08
lightdb=# insert into test_xmin_xmax values(1);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(2);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(3);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(4);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(5);
INSERT 0 1
lightdb=# select * from heap_page_items(get_raw_page('test_xmin_xmax',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 |     28 | 5670928 |      0 |        0 | (0,1)  |           1 |       2048 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 5670936 |      0 |        0 | (0,2)  |           1 |       2048 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 5670956 |      0 |        0 | (0,3)  |           1 |       2048 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 5670968 |      0 |        0 | (0,4)  |           1 |       2048 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 | 5670976 |      0 |        0 | (0,5)  |           1 |       2048 |     24 |        |       | \x05000000
(5 rows)
lightdb=# delete from test_xmin_xmax where id = 3;
DELETE 1
postgres=# select * from heap_page_items(get_raw_page('test_xmin_xmax',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 |     28 | 5670928 |       0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 5670936 |       0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 5670956 | 5671778 |        0 | (0,3)  |        8193 |        256 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 5670968 |       0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 | 5670976 |       0 |        0 | (0,5)  |           1 |       2304 |     24 |        |       | \x05000000
(5 rows)

t_xmin表示插入動作時的tranction id,t_xmax表示delete動作時的 tranction id

session1:

開啟一個事務

lightdb=# begin;
BEGIN
lightdb=*# insert into test values(1);
INSERT 0 1
lightdb=*#

session2:

lightdb=# analyze test;
ANALYZE
lightdb=# \dt test
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+---------
 public | test | table | lightdb
(1 row)
lightdb=# \dt+ test
                           List of relations
 Schema | Name | Type  |  Owner  | Persistence |  Size   | Description 
--------+------+-------+---------+-------------+---------+-------------
 public | test | table | lightdb | permanent   | 1610 MB | 
(1 row)
lightdb=# delete from test ;
DELETE 19999998
lightdb=# \dt+ test
                           List of relations
 Schema | Name | Type  |  Owner  | Persistence |  Size   | Description 
--------+------+-------+---------+-------------+---------+-------------
 public | test | table | lightdb | permanent   | 1610 MB | 
(1 row)
lightdb=# vacuum test;
VACUUM
lightdb=# \dt+ test
                          List of relations
 Schema | Name | Type  |  Owner  | Persistence | Size  | Description 
--------+------+-------+---------+-------------+-------+-------------
 public | test | table | lightdb | permanent   | 24 kB | 
(1 row)

看到可以透過vacuum收縮表

/*
* HeapTupleSatisfiesVacuum
*
*Determine the status of tuples for VACUUM purposes. Here, what
*we mainly want to know is if a tuple is potentially visible to *any*
*running transaction. If so, it can't be removed yet by VACUUM.
*
* OldestXmin is a cutoff XID (obtained from GetOldestXmin()). Tuples
* deleted by XIDs >= OldestXmin are deemed "recently dead"; they might
* still be visible to some open transaction, so we can't remove them,
* even if we see that the deleting transaction has committed.
*/
HTSV_Result
HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,
Buffer buffer)
   
...
  if (!TransactionIdPrecedes(xmax, OldestXmin))
    return HEAPTUPLE_RECENTLY_DEAD;
...

但是在PG14中

已經不錯在這部分邏輯內容,可以收縮表


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29990276/viewspace-2867289/,如需轉載,請註明出處,否則將追究法律責任。

相關文章