Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.