postgres mvcc sql code

databases

CREATE EXTENSION IF NOT EXISTS pageinspect;
 
 
-- Create table sample_table
CREATE TABLE store (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    value INT NOT NULL
);
 
-- Insert rows, about 1M ?
DO $$
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO store (name, value)
        VALUES ('Name_' || i, i * 10);
    END LOOP;
END $$;
 
-- Disable autovacuum for the table
ALTER TABLE store SET (autovacuum_enabled = false);
 
 
------------------------------------------------------------
 
SELECT txid_current();
SELECT txid_current();
 
INSERT INTO store(name, value) VALUES('score', 10);
SELECT xmin, xmax, * FROM store WHERE id=1;
 
SELECT lp, t_ctid, t_xmin, t_xmax, FROM heap_page_items(get_raw_page('store', 0));
 
UPDATE store set value=20 where id=1;
SELECT xmin, xmax, * FROM store WHERE id=1;
SELECT lp, t_ctid, t_xmin, t_xmax FROM heap_page_items(get_raw_page('store', 0));
 
SELECT n_live_tup, n_dead_tup, relname FROM pg_stat_all_tables WHERE relname = 'store';
 
SELECT lp, t_ctid, t_xmin, t_xmax, t_data
FROM heap_page_items(get_raw_page('accounts', 0));
 
 
-------------------------------------------------------------
 
SELECT COUNT(*) FROM sample_table;
select * from sample_table;
 
UPDATE sample_table set value=1283423;
 
select xmin, xmax, * from store where id=1;
 
-- Show number of live and dead tuples in sample_table
select n_live_tup, n_dead_tup, relname from pg_stat_all_tables WHERE relname = 'sample_table';
 
-- Size of table
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables
WHERE
    relname = 'store';
 
-- Manual vacuum
VACUUM FULL sample_table;
 
select txid_current();
 
INSERT INTO sample_table (name, value)
VALUES ('foo', 10);
 
update sample_table set value=30 where name='foo';
 
select * from sample_table;
select xmin, xmax, * from sample_table where name='foo';
 
SELECT lp, t_ctid, t_xmin, t_xmax, t_infomask2
FROM heap_page_items(get_raw_page('sample_table', 0));
 
drop table sample_table;

HEAP TUPLES SELECT * FROM heap_page_items(get_raw_page(‘sample’, 0)) limit 15;

VIEW INDEXES SELECT * FROM bt_page_items(‘sample_pkey’, 1) limit 10;

data in index stores the index value, 0a 00 00 00 00 00 00 00 = 10