-- 00-init.sql
-- ----------------

\echo This script is designed to run in a database called 'test'
\echo and requires installation of /contrib/pageinspect and
\echo /contrib/pg_freespacemap.
\c test

DROP TABLE IF EXISTS mvcc_demo;
-- cannot be a temporary table because other sessions must see it
CREATE TABLE mvcc_demo (val INTEGER);

DROP VIEW IF EXISTS mvcc_demo_page0;
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;

-- 01-xmin_ins.sql
-- ----------------
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);
SELECT xmin, xmax, * FROM mvcc_demo;

-- 02-xmax_del.sql
-- ----------------
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);
SELECT xmin, xmax, * FROM mvcc_demo;

BEGIN WORK;
DELETE FROM mvcc_demo;
SELECT xmin, xmax, * FROM mvcc_demo;
\! psql -e -c 'SELECT xmin, xmax, * FROM mvcc_demo;' | sed 's/^/\t/g'
SELECT txid_current();
COMMIT WORK;

-- 03-xmax_upd.sql
-- ----------------
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);
SELECT xmin, xmax, * FROM mvcc_demo;

BEGIN WORK;
UPDATE mvcc_demo SET val = 2;
SELECT xmin, xmax, * FROM mvcc_demo;
\! psql -e -c 'SELECT xmin, xmax, * FROM mvcc_demo;' | sed 's/^/\t/g'
COMMIT WORK;


-- 04-del-abort.sql
-- ----------------
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);

BEGIN WORK;
DELETE FROM mvcc_demo;
SELECT xmin, xmax, * FROM mvcc_demo;
ROLLBACK WORK;

SELECT xmin, xmax, * FROM mvcc_demo;

-- 05-xmax_lock.sql
-- ----------------
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);

BEGIN WORK;
SELECT xmin, xmax, * FROM mvcc_demo;
SELECT xmin, xmax, * FROM mvcc_demo FOR UPDATE;
SELECT xmin, xmax, * FROM mvcc_demo;
COMMIT WORK;


-- 06-cmin_ins.sql
-- ----------------
DELETE FROM mvcc_demo;

BEGIN WORK;
INSERT INTO mvcc_demo VALUES (1);
INSERT INTO mvcc_demo VALUES (2);
INSERT INTO mvcc_demo VALUES (3);
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
COMMIT WORK;


-- 07-cmin_del.sql
-- ----------------
DELETE FROM mvcc_demo;

BEGIN WORK;
INSERT INTO mvcc_demo VALUES (1);
INSERT INTO mvcc_demo VALUES (2);
INSERT INTO mvcc_demo VALUES (3);
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DELETE FROM mvcc_demo;
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
FETCH ALL FROM c_mvcc_demo;
COMMIT WORK;

-- 08-cmin_upd.sql
-- ----------------
DELETE FROM mvcc_demo;

BEGIN WORK;
INSERT INTO mvcc_demo VALUES (1);
INSERT INTO mvcc_demo VALUES (2);
INSERT INTO mvcc_demo VALUES (3);
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
UPDATE mvcc_demo SET val = val * 10;
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
FETCH ALL FROM c_mvcc_demo;
COMMIT WORK;

-- 09-cmax_upd.sql
-- ----------------
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);
SELECT xmin, xmax, * FROM mvcc_demo;

BEGIN WORK;
INSERT INTO mvcc_demo VALUES (2);
INSERT INTO mvcc_demo VALUES (3);
INSERT INTO mvcc_demo VALUES (4);
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
UPDATE mvcc_demo SET val = val * 10;
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
\! psql -e -c 'SELECT xmin, xmax, cmax, * FROM mvcc_demo;' | sed 's/^/\t/g'
COMMIT WORK;


-- 10-cmin_upd_combo.sql
-- ----------------
-- clear out heap file
TRUNCATE mvcc_demo;

BEGIN WORK;

DELETE FROM mvcc_demo;
DELETE FROM mvcc_demo;
DELETE FROM mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);
INSERT INTO mvcc_demo VALUES (2);
INSERT INTO mvcc_demo VALUES (3);

SELECT xmin, cmin, xmax, * FROM mvcc_demo;

DECLARE c_mvcc_demo CURSOR FOR 
SELECT xmin, xmax, cmax, * FROM mvcc_demo;

UPDATE mvcc_demo SET val = val * 10;
SELECT xmin, cmin, xmax, * FROM mvcc_demo;

FETCH ALL FROM c_mvcc_demo;
-- uses contrib/pageinspect
-- 0x0020 is HEAP_COMBOCID
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;
COMMIT WORK;

-- 20-hot_ins.sql
-- ----------------
-- clear out heap file
TRUNCATE mvcc_demo;

-- allows 256 per page
-- must fill page to trigger HOT, < 10% free
-- index pointers require lp redirect for HOT update

INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);

-- compute free space percentage
SELECT (100 * (upper - lower) / pagesize::float8)::integer AS free_pct
FROM page_header(get_raw_page('mvcc_demo', 0));

INSERT INTO mvcc_demo VALUES (1);
SELECT * FROM mvcc_demo_page0
OFFSET 240;

DELETE FROM mvcc_demo WHERE val > 0;
INSERT INTO mvcc_demo VALUES (2);
SELECT * FROM mvcc_demo_page0
OFFSET 240;

DELETE FROM mvcc_demo WHERE val > 0;
INSERT INTO mvcc_demo VALUES (3);
SELECT * FROM mvcc_demo_page0
OFFSET 240;

-- force pruning
SELECT * FROM mvcc_demo
OFFSET 1000;

SELECT * FROM mvcc_demo_page0
OFFSET 240;

VACUUM mvcc_demo;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

-- 21-hot_upd.sql
-- ----------------
-- clear out heap file
TRUNCATE mvcc_demo;

-- allows 255 per page
-- must fill page to trigger HOT
-- index pointers require lp redirect

INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT INTO mvcc_demo VALUES (1);
SELECT * FROM mvcc_demo_page0
OFFSET 240;

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

-- transaction now committed, HOT chain allows tid to be marked as Unused
SELECT * FROM mvcc_demo
OFFSET 1000;

SELECT * FROM mvcc_demo_page0
OFFSET 240;

VACUUM mvcc_demo;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

-- 30-vacuum.sql
-- ----------------
-- clear out heap file
TRUNCATE mvcc_demo;

INSERT INTO mvcc_demo VALUES (1);
INSERT INTO mvcc_demo VALUES (2);
INSERT INTO mvcc_demo VALUES (3);
SELECT 	ctid, xmin, xmax
FROM mvcc_demo_page0;

DELETE FROM mvcc_demo;
SELECT 	ctid, xmin, xmax
FROM mvcc_demo_page0;
-- too small to trigger auto-vacuum

VACUUM mvcc_demo;

SELECT pg_relation_size('mvcc_demo');

-- 31-vacuum-freesp.sql
-- ----------------
-- clear out heap file
TRUNCATE mvcc_demo;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

INSERT INTO mvcc_demo VALUES (1);

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

INSERT INTO mvcc_demo VALUES (2);

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

DELETE FROM mvcc_demo WHERE val = 2;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

DELETE FROM mvcc_demo WHERE val = 1;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

SELECT pg_relation_size('mvcc_demo');

-- 40-index.sql
-- ----------------
-- indexing non-modified columns has no effect

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

-- 80-index.sql
-- ----------------
-- indexes have no effect on INSERT

-- 81-hot_upd.sql
-- ----------------
-- clear out heap file
TRUNCATE mvcc_demo;

-- allows 255 per page
-- must fill page to trigger HOT
-- index pointers require lp redirect

INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT INTO mvcc_demo VALUES (1);
SELECT * FROM mvcc_demo_page0
OFFSET 240;

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
SELECT * FROM mvcc_demo_page0
OFFSET 240;

-- transaction now committed, HOT chain allows tid to be marked as Unused
SELECT * FROM mvcc_demo
OFFSET 1000;

SELECT * FROM mvcc_demo_page0
OFFSET 240;

VACUUM mvcc_demo;
SELECT * FROM mvcc_demo_page0
OFFSET 240;