PostgreSQL14在做vacuum時候的邏輯判斷是否經過HEAPTUPLE_RECENTLY_DEAD
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ruby邏輯判斷符號符號
- c#學習----邏輯判斷C#
- 點選tabbarItem的時候判斷使用者是否處於登入狀態tabBar
- 判斷某過程是否在執行的方法
- 用程式判斷url連結時候有效
- JavaScript(ES6)邏輯判斷條件優化JavaScript優化
- jquery判斷按鈕是否被點選過jQuery
- 通過模板判斷Value是否為指標指標
- 在程式設計中思考,簡化你的判斷邏輯程式設計
- [BUG反饋]模型編輯模板存在條件邏輯判斷錯誤模型
- 02 . Shell變數和邏輯判斷及迴圈使用變數
- vue router+ vuex+ 首頁登入判斷邏輯Vue
- 如何判斷程式設計師在做什麼?程式設計師
- 小小邏輯判斷符的錯誤使用,資損幾萬塊
- 判斷checkbox是否已經勾選設定按鈕是否可用
- 在做一個專案,在做倉儲分析的時候被搞暈了
- js判斷時間格式是否正確程式碼JS
- sqlserver中判斷表或臨時表是否存在SQLServer
- js如何判斷頁面是否已經載入完成JS
- 前端業務程式碼配置化處理條件判斷邏輯前端
- JS 寫邏輯判斷,不要只知道用 if-else 和 switchJS
- 判斷字串是否為空字串
- 判斷URL字串是否合法字串
- 41:判斷元素是否存在
- 判斷oracle是否啟動Oracle
- Javascript 判斷物件是否相等JavaScript物件
- 判斷SD卡是否可用SD卡
- 判斷字串是否唯一字串
- golang中判斷兩個slice是否相等與判斷值下的 陣列是否相等Golang陣列
- JavaScript判斷表單元素value值是否被修改過JavaScript
- 判斷一個物件是否為空物件,判斷一個物件中是否有空值物件
- 判斷網路是否連線
- JavaScript 判斷是否是陣列JavaScript陣列
- JavaScript 判斷函式是否存在JavaScript函式
- python判斷是否為listPython
- js判斷字串是否為空JS字串
- mysql如何判斷是否為空MySql
- postgresql如何判斷表是否存在SQL