Thread: [Postgres-xc-developers] Cursor visibility problem and its suggested solution

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi,

In a multi-statement transaction each statement is given a command
identifier
starting from zero and incrementing for each statement.
These command indentifers are required for extra tracking because each
statement has its own visibility rules with in the transaction.
For example, a cursor’s contents must remain unchanged even if later
statements in the
same transaction modify rows.  Such tracking is implemented using system
command id
columns cmin/cmax, which is internally actually is a single column.

cmin/cmax come into play in case of multi-statement transactions only,
they are both zero otherwise.

cmin "The command identifier of the statement within the inserting
transaction."
cmax "The command identifier of the statement within the deleting
transaction."

Here are the visibility rules (taken from comments of tqual.c)

(                                        // A heap tuple is valid "now" iff
  Xmin == my-transaction &&              // inserted by the current
transaction
  Cmin < my-command &&                   // before this command, and
  (
    Xmax is null ||                      // the row has not been deleted, or
    (
      Xmax == my-transaction &&          // it was deleted by the current
transaction
      Cmax >= 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
      )
    )
  )
)

Because cmin and cmax are internally a single system column,
it is therefore not possible to simply record the status of a row
that is created and expired in the same multi-statement transaction.
For that reason, a special combo command id is created that references
a local memory hash that contains the actual cmin and cmax values.
It means that if combo id is being used the number we are seeing
would not be the cmin or cmax it will be an index into a local
array that contains a structure with has the actual cmin and cmax values.

The following queries (taken mostly from
http://momjian.us/main/writings/pgsql/mvcc.pdf)
use the contrib module pageinspect, which allows
visibility of internal heap page structures and all stored rows,
including those not visible in the current snapshot.
(Bit 0x0020 is defined as HEAP_COMBOCID.)

We are exploring 3 examples here:
1) INSERT & DELETE in a single transaction
2) INSERT & UPDATE in a single transaction
3) INSERT from two different transactions & UPDATE from one

test=# drop table mvcc_demo;
DROP TABLE
test=#
test=# create table mvcc_demo (val int);
CREATE TABLE
test=#
test=# TRUNCATE mvcc_demo;
TRUNCATE TABLE
test=#
test=# BEGIN;
BEGIN
test=# DELETE FROM mvcc_demo; -- increment command id to show that combo id
would be different
DELETE 0
test=# DELETE FROM mvcc_demo;
DELETE 0
test=# DELETE FROM mvcc_demo;
DELETE 0
test=# INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
test=# INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
test=# INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
test=# SELECT t_xmin AS xmin,
test-#        t_xmax::text::int8 AS xmax,
test-#        t_field3::text::int8 AS cmin_cmax,
test-#        (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
test-# ORDER BY 2 DESC, 3;
 xmin  | xmax | cmin_cmax | is_combocid
-------+------+-----------+-------------
 80685 |    0 |         3 | f
 80685 |    0 |         4 | f
 80685 |    0 |         5 | f
(3 rows)

test=#
test=# DELETE FROM mvcc_demo;
DELETE 3
test=# SELECT t_xmin AS xmin,
test-#        t_xmax::text::int8 AS xmax,
test-#        t_field3::text::int8 AS cmin_cmax,
test-#        (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
test-# ORDER BY 2 DESC, 3;
 xmin  | xmax  | cmin_cmax | is_combocid
-------+-------+-----------+-------------
 80685 | 80685 |         0 | t
 80685 | 80685 |         1 | t
 80685 | 80685 |         2 | t
(3 rows)

Note that since is_combocid is true the numbers are not cmin/cmax they are
actually
the indexes of the internal array already explained above.
combo id index 0 would contain cmin 3, cmax 6
combo id index 1 would contain cmin 4, cmax 6
combo id index 2 would contain cmin 5, cmax 6

test=#
test=# END;
COMMIT
test=#
test=#
test=# TRUNCATE mvcc_demo;
TRUNCATE TABLE
test=#
test=#
test=#
test=# BEGIN;
BEGIN
test=# INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
test=# INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
test=# INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
test=# SELECT t_xmin AS xmin,
test-#        t_xmax::text::int8 AS xmax,
test-#        t_field3::text::int8 AS cmin_cmax,
test-#        (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
test-# ORDER BY 2 DESC, 3;
 xmin  | xmax | cmin_cmax | is_combocid
-------+------+-----------+-------------
 80675 |    0 |         0 | f
 80675 |    0 |         1 | f
 80675 |    0 |         2 | f
(3 rows)

test=#
test=# UPDATE mvcc_demo SET val = val * 10;
UPDATE 3
test=#
test=# SELECT t_xmin AS xmin,
test-#        t_xmax::text::int8 AS xmax,
test-#        t_field3::text::int8 AS cmin_cmax,
test-#        (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
test-# ORDER BY 2 DESC, 3;
 xmin  | xmax  | cmin_cmax | is_combocid
-------+-------+-----------+-------------
 80675 | 80675 |         0 | t
 80675 | 80675 |         1 | t
 80675 | 80675 |         2 | t
 80675 |     0 |         3 | f
 80675 |     0 |         3 | f
 80675 |     0 |         3 | f
(6 rows)

test=#
test=# END;
COMMIT
test=#
test=#
test=# TRUNCATE mvcc_demo;
TRUNCATE TABLE
test=#

    -- From one psql issue
    test=# INSERT INTO mvcc_demo VALUES (1);
    INSERT 0 1
    test=# SELECT t_xmin AS xmin,
    test-#        t_xmax::text::int8 AS xmax,
    test-#        t_field3::text::int8 AS cmin_cmax,
    test-#        (t_infomask::integer & X'0020'::integer)::bool AS
is_combocid
    test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
    test-# ORDER BY 2 DESC, 3;
     xmin  | xmax | cmin_cmax | is_combocid
    -------+------+-----------+-------------
     80677 |    0 |         0 | f
    (1 row)

test=# -- From another issue
test=# BEGIN;
BEGIN
test=# INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
test=# INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
test=# INSERT INTO mvcc_demo VALUES (4);
INSERT 0 1
test=# SELECT t_xmin AS xmin,
test-#        t_xmax::text::int8 AS xmax,
test-#        t_field3::text::int8 AS cmin_cmax,
test-#        (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
test-# ORDER BY 2 DESC, 3;
 xmin  | xmax | cmin_cmax | is_combocid
-------+------+-----------+-------------
 80677 |    0 |         0 | f
 80678 |    0 |         0 | f
 80678 |    0 |         1 | f
 80678 |    0 |         2 | f
(4 rows)

test=#
test=# UPDATE mvcc_demo SET val = val * 10;
UPDATE 4
test=# SELECT t_xmin AS xmin,
test-#        t_xmax::text::int8 AS xmax,
test-#        t_field3::text::int8 AS cmin_cmax,
test-#        (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
test-# ORDER BY 2 DESC, 3;
 xmin  | xmax  | cmin_cmax | is_combocid
-------+-------+-----------+-------------
 80678 | 80678 |         0 | t
 80678 | 80678 |         1 | t
 80678 | 80678 |         2 | t
 80677 | 80678 |         3 | f
 80678 |     0 |         3 | f
 80678 |     0 |         3 | f
 80678 |     0 |         3 | f
 80678 |     0 |         3 | f
(8 rows)

test=#

    test=# -- Before finishing this, issue these from the first psql
    test=# SELECT t_xmin AS xmin,
    test-#        t_xmax::text::int8 AS xmax,
    test-#        t_field3::text::int8 AS cmin_cmax,
    test-#        (t_infomask::integer & X'0020'::integer)::bool AS
is_combocid
    test-# FROM heap_page_items(get_raw_page('mvcc_demo', 0))
    test-# ORDER BY 2 DESC, 3;
     xmin  | xmax  | cmin_cmax | is_combocid
    -------+-------+-----------+-------------
     80678 | 80678 |         0 | t
     80678 | 80678 |         1 | t
     80678 | 80678 |         2 | t
     80677 | 80678 |         3 | f
     80678 |     0 |         3 | f
     80678 |     0 |         3 | f
     80678 |     0 |         3 | f
     80678 |     0 |         3 | f
    (8 rows)

test=# END;
COMMIT

Now consider the case we are trying to solve

drop table tt1;
create table tt1(f1 int);

BEGIN;
 insert into tt1 values(1);
 declare c50 cursor for select * from tt1; -- should show one row only
 insert into tt1 values(2);
 fetch all from c50;
COMMIT;

Consider Data node 1 log

(a) [exec_simple_query][1026][START TRANSACTION ISOLATION LEVEL read
committed READ WRITE]
(b) [exec_simple_query][1026][drop table tt1;]
(c) [exec_simple_query][1026][PREPARE TRANSACTION 'T21075']
(d) [exec_simple_query][1026][COMMIT PREPARED 'T21075']
(e) [exec_simple_query][1026][START TRANSACTION ISOLATION LEVEL read
committed READ WRITE]
(f) [exec_simple_query][1026][create table tt1(f1 int);]
(g) [exec_simple_query][1026][PREPARE TRANSACTION 'T21077']
(h) [exec_simple_query][1026][COMMIT PREPARED 'T21077']
(i) [exec_simple_query][1026][START TRANSACTION ISOLATION LEVEL read
committed READ WRITE]
(j) [exec_simple_query][1026][INSERT INTO tt1 (f1) VALUES (1)]
(k) [exec_simple_query][1026][INSERT INTO tt1 (f1) VALUES (2)]
(l) [PostgresMain][4155][SELECT tt1.f1, tt1.ctid, pgxc_node_str() FROM tt1]
(m) [exec_simple_query][1026][COMMIT TRANSACTION]

The cursor currently shows both inserted rows because command id at data
node in
step (j) is 0
step (k) is 1 &
step (l) is 2

Where as we need command ids to be

step (j) should be 0
step (k) should be 2 &
step (l) should be 1

This will solve the cursor visibility problem.

To implement this I suggest we send command IDs to data nodes from the
coordinator
like we send gxid. The only difference will be that we do not need to take
command IDs
from GTM since they are only valid with in the transaction.

See this example

test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin | xmax | cmin | cmax | f1
------+------+------+------+----
(0 rows)

test=# begin;
BEGIN
test=#  insert into tt1 values(1);
INSERT 0 1
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
(1 row)

test=#  insert into tt1 values(2);
INSERT 0 1
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
 80615 |    0 |    1 |    1 |  2
(2 rows)

test=#  insert into tt1 values(3);
INSERT 0 1
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
 80615 |    0 |    1 |    1 |  2
 80615 |    0 |    2 |    2 |  3
(3 rows)

test=#  insert into tt1 values(4);
INSERT 0 1
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
 80615 |    0 |    1 |    1 |  2
 80615 |    0 |    2 |    2 |  3
 80615 |    0 |    3 |    3 |  4
(4 rows)

test=# end;
COMMIT
test=#
test=#
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
 80615 |    0 |    1 |    1 |  2
 80615 |    0 |    2 |    2 |  3
 80615 |    0 |    3 |    3 |  4
(4 rows)

test=#  insert into tt1 values(5);
INSERT 0 1
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
 80615 |    0 |    1 |    1 |  2
 80615 |    0 |    2 |    2 |  3
 80615 |    0 |    3 |    3 |  4
 80616 |    0 |    0 |    0 |  5
(5 rows)

test=#  insert into tt1 values(6);
INSERT 0 1
test=#
test=#
test=# select xmin,xmax,cmin,cmax,* from tt1;
 xmin  | xmax | cmin | cmax | f1
-------+------+------+------+----
 80615 |    0 |    0 |    0 |  1
 80615 |    0 |    1 |    1 |  2
 80615 |    0 |    2 |    2 |  3
 80615 |    0 |    3 |    3 |  4
 80616 |    0 |    0 |    0 |  5
 80617 |    0 |    0 |    0 |  6
(6 rows)

Note that at the end of the multi-statement transaction the command id gets
reset to zero.

--
Abbas
Architect
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Thread: [Postgres-xc-developers] Cursor visibility problem and its suggested solution

postgres-xc-developers