How to showcase the work of MVCC with several parallel sessions in PostgreSQL?

Question

I need to demonstrate the work of MVCC in PostgreSQL with the help of parallel sessions that will access the same table at the same time.

I have no idea how to do it. Tell me, please.

Answer 1

Session 1:

CREATE TABLE test(id integer);

INSERT INTO test VALUES (1);

START TRANSACTION;

SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,1) | 5163 |    0 |  1
(1 row)

This row version was created by transaction 5163. It is item 1 in table block 0.

UPDATE test SET id = 2;

SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,2) | 5164 |    0 |  2
(1 row)

The update inserts a new row version. This is item 2 in table block 0, created by transaction 5164 (this transaction).

Session 2:

SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,1) | 5163 | 5164 |  1
(1 row)

Session 2 still sees the old row version, because the deleting transaction 5164 has not yet committed.

Session 1:

COMMIT;

Session 2:

SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,2) | 5164 |    0 |  2
(1 row)

Now that session 1 has committed, session 2 sees the new row version as well.