(unnamed)

text/plain

Download

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

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)