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

This article mainly introduces "what is the basic logic of Tuple visibility judgment in PostgreSQL". In daily operation, I believe that many people have doubts about the basic logic of Tuple visibility judgment in PostgreSQL. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the questions of "what is the basic logic of Tuple visibility judgment in PostgreSQL"! Next, please follow the editor to study!

Judgment logic

(Xmin = = my-transaction & & inserted by the current transaction

Cmin

= my-command) but not before this command

| | or |

(Xmin is committed & & the row was inserted by a committed transaction, and

(Xmax is null | | the row has not been deleted, or

(Xmax = = my-transaction & & the row is being deleted by this transaction

Cmax > = my-command) | | but it's not deleted "yet", or

(Xmax! = my-transaction & & the row was deleted by another transaction

Xmax is not committed) that has not been committed

Among them, my-command is reflected in PG through cmin/cmax.

Cmin/cmax

As shown in the following example, the cursor is defined before the delete command, so the Tuple is visible.

10:51:21 (xdb@ [local]: 5432) testdb=# DELETE FROM mvcc_demo;DELETE 310 xdb@ [local]: 5432) testdb=# 10:51:22 (xdb@ [local]: 5432) testdb=# BEGIN WORK;BEGIN10:51:22 (xdb@ [local]: 5432) testdb=#* INSERT INTO mvcc_demo VALUES (1); INSERT 0 110 xdb@ [local]: 5432) testdb=#* INSERT INTO mvcc_demo VALUES (2) INSERT 0 110 testdb=#* INSERT INTO mvcc_demo VALUES 22 (xdb@ [local]: 5432) testdb=#* INSERT INTO mvcc_demo VALUES (3); INSERT 0 110 testdb=#* INSERT INTO mvcc_demo VALUES 22 (xdb@ [local]: 5432) testdb=#* SELECT xmin, cmin, xmax, cmax, * FROM mvcc_demo Xmin | cmin | xmax | cmax | val-+-2400 | 0 | 0 | 0 | 1 2400 | 1 | 0 | 1 | 2 | 2 | 0 | 2 | 3 rows 10:51:23 (xdb@ [local]: 5432) testdb=#* DECLARE c_mvcc_demo CURSOR FOR [local] xdb@testdb-# SELECT xmin, cmin, xmax, cmax * FROM mvcc_demo -- > define cursor DECLARE CURSOR10:51:34 (xdb@ [local]: 5432) testdb=#* DELETE FROM mvcc_demo;-- > Delete data DELETE 310 DELETE 51 testdb=#* DELETE FROM mvcc_demo; 34 (xdb@ [local]: 5432) testdb=#* SELECT xmin, cmin, xmax, cmax, * FROM mvcc_demo Xmin | cmin | xmax | cmax | val-+-(0 rows) 10:51:35 (xdb@ [local]: 5432) testdb=#* FETCH ALL FROM c_mvcc_demo -- > the cursor is defined before deleting the command, so you can see xmin | cmin | xmax | cmax | val-+-2400 | 0 | 2400 | 0 | 1 2400 | 1 | 2400 | 1 | 2 2400 | 2 | 2400 | 2 | 3 rows). The study on "what is the basic logic of Tuple visibility judgment in PostgreSQL" is over. I hope I can solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.