Judgment and Analysis of Tuple visibility in PostgreSQL
This article mainly talks about "Tuple visibility judgment and Analysis in PostgreSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "Tuple visibility judgment Analysis in PostgreSQL".
Xmin/xmax is the current transaction
Tuple.xmin or xmax is the case of the current transaction, and the transaction status is IN_PROGRESS. The judgment logic is as follows:
Insert unsubmitted
If xmin = = current transaction Then
If xmax = = current transaction Then
Tuples are not visible
Else
Tuple visible
End If
End If
As shown in the following example:
15:40:39 (xdb@ [local]: 5432) testdb=# 15:40:40 (xdb@ [local]: 5432) testdb=# begin;BEGIN15:40:44 (xdb@ [local]: 5432) testdb=#* insert into t_session1 values (1); INSERT 0 115 40 local 52 (xdb@ [local]: 5432) testdb=#* update t_session1 set id = 0 UPDATE 115 local 41 local 02 (xdb@ [local]: 5432) testdb=#* select lp,lp_off,t_xmin,t_xmax from heap_page_items (get_raw_page ('tweets session 1 session 0)) Lp | lp_off | t_xmin | t_xmax-+-1 | 8160 | 2370 | 2370-> 2370 insert data, 2370 update data, the Tuple is not visible 2 | 8128 | 2370 | 0-> 2370 updated data, the Tuple is visible (2 rows)
Insert submitted
If xmax = = current transaction Then
Tuples are not visible
End If
15:41:11 (xdb@ [local]: 5432) testdb=#* delete from tactile session 1 = delete 115 xdb@ [local]: 5432) testdb=#* select lp,lp_off,t_xmin,t_xmax from heap_page_items (get_raw_page ('tours session 1 session session 0)) Lp | lp_off | t_xmin | t_xmax-- +-1 | 8160 | 2370 | 2370-> 2370 update the Tuple, invisible 2 | 8128 | 2370 | 2370-> 2370 Delete the Tuple, invisible (2 rows) 15:41:38 (xdb@ [local]: 5432) testdb=#* commit;COMMIT
Note: in this case (xmax = = current transaction), the xmin state cannot be ABORTED because it is impossible to Update/Delete a tuple that does not exist (a transaction that is not committed can be considered nonexistent).
II. Xmin&xmax non-current transaction
Neither xmin nor xmax is the current transaction. Assuming that the snapshot is ST1:ST2:XIP [], the judgment logic is as follows:
If xmin.STATUS = = COMMITTED Then
If xmax < ST1 & & xmax.STATUS = = COMMITTED Then
Tuples are not visible
If xmax ∉ XIP [] & & xmax.STATUS = = COMMITTED Then
Tuples are not visible
Else
Tuple visible
End If
Else
Tuples are not visible
End If
At this point, I believe you have a deeper understanding of "Tuple visibility judgment and Analysis in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
