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