00-init.sql
------------------
This script is designed to run in a database called test
and requires installation of /contrib/pageinspect and
/contrib/pg_freespacemap.
You are now connected to database "test" as user "postgres".
DROP TABLE IF EXISTS mvcc_demo;
DROP TABLE
CREATE TABLE mvcc_demo (val INTEGER);
CREATE TABLE
DROP VIEW IF EXISTS mvcc_demo_page0;
DROP VIEW
CREATE VIEW mvcc_demo_page0 AS
	SELECT  '(0,' || lp || ')' AS ctid,
	        CASE lp_flags
	                WHEN 0 THEN 'Unused'
	                WHEN 1 THEN 'Normal'
	                WHEN 2 THEN 'Redirect to ' || lp_off
	                WHEN 3 THEN 'Dead'
	        END,
	        t_xmin::text::int8 AS xmin,
	        t_xmax::text::int8 AS xmax, 
		t_ctid
	FROM heap_page_items(get_raw_page('mvcc_demo', 0))
	ORDER BY lp;
CREATE VIEW

01-xmin_ins.sql
------------------
DELETE FROM mvcc_demo;
DELETE 0
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1301 |    0 |   1
(1 row)


02-xmax_del.sql
------------------
DELETE FROM mvcc_demo;
DELETE 1
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1303 |    0 |   1
(1 row)

BEGIN WORK;
BEGIN
DELETE FROM mvcc_demo;
DELETE 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
(0 rows)

	SELECT xmin, xmax, * FROM mvcc_demo;
	 xmin | xmax | val 
	------+------+-----
	 1303 | 1304 |   1
	(1 row)
	
COMMIT WORK;
COMMIT

03-xmax_upd.sql
------------------
DELETE FROM mvcc_demo;
DELETE 0
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1305 |    0 |   1
(1 row)

BEGIN WORK;
BEGIN
UPDATE mvcc_demo SET val = 2;
UPDATE 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1306 |    0 |   2
(1 row)

	SELECT xmin, xmax, * FROM mvcc_demo;
	 xmin | xmax | val 
	------+------+-----
	 1305 | 1306 |   1
	(1 row)
	
COMMIT WORK;
COMMIT

04-xmax_lock.sql
------------------
DELETE FROM mvcc_demo;
DELETE 1
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
BEGIN WORK;
BEGIN
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1308 |    0 |   1
(1 row)

SELECT xmin, xmax, * FROM mvcc_demo FOR UPDATE;
 xmin | xmax | val 
------+------+-----
 1308 |    0 |   1
(1 row)

SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1308 | 1309 |   1
(1 row)

COMMIT WORK;
COMMIT

05-cmin_ins.sql
------------------
DELETE FROM mvcc_demo;
DELETE 1
BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1311 |    0 |    0 |   1
 1311 |    1 |    0 |   2
 1311 |    2 |    0 |   3
(3 rows)

COMMIT WORK;
COMMIT

06-cmin_del.sql
------------------
DELETE FROM mvcc_demo;
DELETE 3
BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1313 |    0 |    0 |   1
 1313 |    1 |    0 |   2
 1313 |    2 |    0 |   3
(3 rows)

DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DECLARE CURSOR
DELETE FROM mvcc_demo;
DELETE 3
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
(0 rows)

FETCH ALL FROM c_mvcc_demo;
 xmin | xmax | cmax | val 
------+------+------+-----
 1313 | 1313 |    0 |   1
 1313 | 1313 |    1 |   2
 1313 | 1313 |    2 |   3
(3 rows)

COMMIT WORK;
COMMIT

07-cmin_upd.sql
------------------
DELETE FROM mvcc_demo;
DELETE 0
BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1314 |    0 |    0 |   1
 1314 |    1 |    0 |   2
 1314 |    2 |    0 |   3
(3 rows)

DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DECLARE CURSOR
UPDATE mvcc_demo SET val = val * 10;
UPDATE 3
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1314 |    3 |    0 |  10
 1314 |    3 |    0 |  20
 1314 |    3 |    0 |  30
(3 rows)

FETCH ALL FROM c_mvcc_demo;
 xmin | xmax | cmax | val 
------+------+------+-----
 1314 | 1314 |    0 |   1
 1314 | 1314 |    1 |   2
 1314 | 1314 |    2 |   3
(3 rows)

COMMIT WORK;
COMMIT

09-cmax_upd.sql
------------------
DELETE FROM mvcc_demo;
DELETE 3
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val 
------+------+-----
 1316 |    0 |   1
(1 row)

BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (4);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1316 |    0 |    0 |   1
 1317 |    0 |    0 |   2
 1317 |    1 |    0 |   3
 1317 |    2 |    0 |   4
(4 rows)

UPDATE mvcc_demo SET val = val * 10;
UPDATE 4
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1317 |    3 |    0 |  10
 1317 |    3 |    0 |  20
 1317 |    3 |    0 |  30
 1317 |    3 |    0 |  40
(4 rows)

	SELECT xmin, xmax, cmax, * FROM mvcc_demo;
	 xmin | xmax | cmax | val 
	------+------+------+-----
	 1316 | 1317 |    3 |   1
	(1 row)
	
COMMIT WORK;
COMMIT

10-cmin_upd_combo.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
BEGIN WORK;
BEGIN
DELETE FROM mvcc_demo;
DELETE 0
DELETE FROM mvcc_demo;
DELETE 0
DELETE FROM mvcc_demo;
DELETE 0
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1319 |    3 |    0 |   1
 1319 |    4 |    0 |   2
 1319 |    5 |    0 |   3
(3 rows)

DECLARE c_mvcc_demo CURSOR FOR 
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DECLARE CURSOR
UPDATE mvcc_demo SET val = val * 10;
UPDATE 3
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val 
------+------+------+-----
 1319 |    6 |    0 |  10
 1319 |    6 |    0 |  20
 1319 |    6 |    0 |  30
(3 rows)

FETCH ALL FROM c_mvcc_demo;
 xmin | xmax | cmax | val 
------+------+------+-----
 1319 | 1319 |    0 |   1
 1319 | 1319 |    1 |   2
 1319 | 1319 |    2 |   3
(3 rows)

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

COMMIT WORK;
COMMIT

20-hot_ins.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT 0 240
SELECT (100 * (upper - lower) / pagesize::float8)::integer AS free_pct
FROM page_header(get_raw_page('mvcc_demo', 0));
 free_pct 
----------
        6
(1 row)

INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Normal | 1322 |    0 | (0,241)
(1 row)

DELETE FROM mvcc_demo WHERE val > 0;
DELETE 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Normal | 1322 | 1323 | (0,241)
 (0,242) | Normal | 1324 |    0 | (0,242)
(2 rows)

DELETE FROM mvcc_demo WHERE val > 0;
DELETE 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Dead   |      |      | 
 (0,242) | Normal | 1324 | 1325 | (0,242)
 (0,243) | Normal | 1326 |    0 | (0,243)
(3 rows)

SELECT * FROM mvcc_demo
OFFSET 1000;
 val 
-----
(0 rows)

SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Dead   |      |      | 
 (0,242) | Dead   |      |      | 
 (0,243) | Normal | 1326 |    0 | (0,243)
(3 rows)

VACUUM mvcc_demo;
VACUUM
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Unused |      |      | 
 (0,242) | Unused |      |      | 
 (0,243) | Normal | 1326 |    0 | (0,243)
(3 rows)


21-hot_upd.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT 0 240
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Normal | 1329 |    0 | (0,241)
(1 row)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Normal | 1329 | 1330 | (0,242)
 (0,242) | Normal | 1330 |    0 | (0,242)
(2 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid  
---------+-----------------+------+------+---------
 (0,241) | Redirect to 242 |      |      | 
 (0,242) | Normal          | 1330 | 1331 | (0,243)
 (0,243) | Normal          | 1331 |    0 | (0,243)
(3 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid  
---------+-----------------+------+------+---------
 (0,241) | Redirect to 243 |      |      | 
 (0,242) | Normal          | 1332 |    0 | (0,242)
 (0,243) | Normal          | 1331 | 1332 | (0,242)
(3 rows)

SELECT * FROM mvcc_demo
OFFSET 1000;
 val 
-----
(0 rows)

SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid  
---------+-----------------+------+------+---------
 (0,241) | Redirect to 242 |      |      | 
 (0,242) | Normal          | 1332 |    0 | (0,242)
 (0,243) | Unused          |      |      | 
(3 rows)

VACUUM mvcc_demo;
VACUUM
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid  
---------+-----------------+------+------+---------
 (0,241) | Redirect to 242 |      |      | 
 (0,242) | Normal          | 1332 |    0 | (0,242)
 (0,243) | Unused          |      |      | 
(3 rows)


30-vacuum.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT 	ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax 
-------+------+------
 (0,1) | 1334 |    0
 (0,2) | 1335 |    0
 (0,3) | 1336 |    0
(3 rows)

DELETE FROM mvcc_demo;
DELETE 3
SELECT 	ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax 
-------+------+------
 (0,1) | 1334 | 1337
 (0,2) | 1335 | 1337
 (0,3) | 1336 | 1337
(3 rows)

VACUUM mvcc_demo;
VACUUM
SELECT pg_relation_size('mvcc_demo');
 pg_relation_size 
------------------
                0
(1 row)


31-vacuum-freesp.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
(0 rows)

INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
 (0,8128)
(1 row)

INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
 (0,8096)
(1 row)

INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
 (0,8064)
(1 row)

DELETE FROM mvcc_demo WHERE val = 3;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
 (0,8096)
(1 row)

DELETE FROM mvcc_demo WHERE val = 2;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
 (0,8096)
(1 row)

SELECT * FROM mvcc_demo_page0;
 ctid  |  case  | xmin | xmax | t_ctid 
-------+--------+------+------+--------
 (0,1) | Normal | 1339 |    0 | (0,1)
 (0,2) | Unused |      |      | 
 (0,3) | Unused |      |      | 
(3 rows)

DELETE FROM mvcc_demo WHERE val = 1;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace 
--------------
(0 rows)

VACUUM mvcc_demo;
VACUUM
SELECT pg_relation_size('mvcc_demo');
 pg_relation_size 
------------------
                0
(1 row)


40-index.sql
------------------

70-index-val.sql
------------------
CREATE INDEX i_mvcc_demo_val on mvcc_demo (val);
CREATE INDEX

80-index.sql
------------------

81-hot_upd.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT 0 240
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Normal | 1348 |    0 | (0,241)
(1 row)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Normal | 1348 | 1349 | (0,242)
 (0,242) | Normal | 1349 |    0 | (0,242)
(2 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Dead   |      |      | 
 (0,242) | Normal | 1349 | 1350 | (0,243)
 (0,243) | Normal | 1350 |    0 | (0,243)
(3 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Dead   |      |      | 
 (0,242) | Dead   |      |      | 
 (0,243) | Normal | 1350 | 1351 | (0,244)
 (0,244) | Normal | 1351 |    0 | (0,244)
(4 rows)

SELECT * FROM mvcc_demo
OFFSET 1000;
 val 
-----
(0 rows)

SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Dead   |      |      | 
 (0,242) | Dead   |      |      | 
 (0,243) | Dead   |      |      | 
 (0,244) | Normal | 1351 |    0 | (0,244)
(4 rows)

VACUUM mvcc_demo;
VACUUM
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid  
---------+--------+------+------+---------
 (0,241) | Unused |      |      | 
 (0,242) | Unused |      |      | 
 (0,243) | Unused |      |      | 
 (0,244) | Normal | 1351 |    0 | (0,244)
(4 rows)


90-vacuum.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT 	ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax 
-------+------+------
 (0,1) | 1353 |    0
 (0,2) | 1354 |    0
 (0,3) | 1355 |    0
(3 rows)

DELETE FROM mvcc_demo;
DELETE 3
SELECT 	ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax 
-------+------+------
 (0,1) | 1353 | 1356
 (0,2) | 1354 | 1356
 (0,3) | 1355 | 1356
(3 rows)

VACUUM mvcc_demo;
VACUUM
SELECT pg_relation_size('mvcc_demo');
 pg_relation_size 
------------------
                0
(1 row)