public
Last active

example script demonstrating heap-only tuples on postgresql; you need the pageinspect extension installed

  • Download Gist
pg_hot_example.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
create temp table t1(t1_id serial primary key, reference varchar(16) not null unique, value varchar(16) not null);
copy t1(reference, value) from stdin;
FOO foo
BAR bar
QUUX quux
\.
 
create temp view t1_combined as
select t1_id, reference, value, ctid, lp_flags, lp_off, case when t_ctid <> ctid then t_ctid end as t_ctid,
t_xmin, xmin_visible, case when t_xmax::text <> '0' then t_xmax end as t_xmax, xmax_visible,
xmin_visible and (xmax_visible is null or not xmax_visible or t_locked <> '') as visible, t_hot_updated, t_heap_only
from (select *,
t_xmin_valid and txid_visible_in_snapshot(t_xmin::text::bigint, txid_current_snapshot()) as xmin_visible,
t_xmax_valid and txid_visible_in_snapshot(t_xmax::text::bigint, txid_current_snapshot()) as xmax_visible
from (select ('(' || 0 || ',' || lp || ')')::tid as ctid,
lp, lp_off, case lp_flags when 0 then 'UNUSED' when 1 then 'NORMAL' when 2 then 'REDIRECT' when 3 then 'DEAD' end as lp_flags,
lp_len, t_xmin, t_xmax, t_field3, t_ctid, (t_infomask&1)<>0 as t_hasnull, (t_infomask&2)<>0 as t_hasvarwidth,
(t_infomask&4)<>0 as t_hasexternal, (t_infomask&8)<>0 as t_hasoid, (t_infomask&32)<>0 as t_combocid,
case t_infomask & 192 when 64 then 'EXCL' when 128 then 'SHARE' when 0 then '' when 192 then 'INVALID' end as t_locked,
(t_infomask&256)<>0 as t_xmin_committed, (t_infomask&512)=0 as t_xmin_valid,
(t_infomask&1024)<>0 as t_xmax_committed, (t_infomask&2048)=0 as t_xmax_valid,
(t_infomask&4096)<>0 as t_xmax_is_multi, (t_infomask&8192)<>0 as t_updated,
(t_infomask&16384)<>0 as t_moved_off, (t_infomask&32768)<>0 as t_moved_in,
t_infomask2&2047 as t_natts, (t_infomask2&16384)<>0 as t_hot_updated,
(t_infomask2&32768)<>0 as t_heap_only,
t_hoff, t_bits, t_oid
from heap_page_items(get_raw_page('t1', 0))) format_heap_page_items
) heap
full outer join (select ctid, * from t1) t1 using (ctid);
 
create temp view t1_indices as
select ctid, pkey_content.itemoffset as pkey_itemoffset, pkey_content.data as pkey_data, auxkey_content.itemoffset as auxkey_itemoffset, auxkey_content.data as auxkey_data
from bt_page_items('t1_pkey', 1) pkey_content
full outer join bt_page_items('t1_reference_key', 1) auxkey_content using (ctid);
 
\echo ********************************************************************************
\echo * Initial table
\echo
select * from t1_combined;
select * from t1_indices;
 
\echo ********************************************************************************
\echo * Update non-indexed column
\echo * - index entries untouched
\echo * - old tuple at ctid (0,1) has t_hot_updated set
\echo * - new tuple at ctid (0,4) has t_heap_only set
\echo * - t_ctid of (0,1) points to (0,4)
\echo
 
begin;
update t1 set value = 'mumble' where t1_id = 1;
end;
 
select * from t1_combined;
select * from t1_indices;
 
\echo ********************************************************************************
\echo * Update non-indexed column again
\echo * - tuple at ctid (0,4) now just points to ctid (0,5) and is redundant
\echo
 
begin;
update t1 set value = 'womble' where t1_id = 1;
end;
 
select * from t1_combined;
select * from t1_indices;
 
\echo ********************************************************************************
\echo * Vacuum table
\echo * - line pointer ctid (0,1) converted to REDIRECT since index entries still point to it
\echo * - redundant tuple at ctid (0,4) reclaimed for reuse
\echo
 
vacuum t1;
 
select * from t1_combined;
select * from t1_indices;
 
\echo ********************************************************************************
\echo * Update indexed column
\echo * - New index entries written for new tuple at ctid (0,4) which is now reused
\echo
 
update t1 set reference = 'WOMBLE' where t1_id = 1;
 
select * from t1_combined;
select * from t1_indices;
 
\echo ********************************************************************************
\echo * Update indexed column to contain same value
\echo * - even though indexed column is mentioned in update, this makes a heap-only change
\echo * - current version is now (0,6) but indices still indicate (0,4)
\echo
 
update t1 set reference = 'WOMBLE', value = 'womble2' where t1_id = 1;
 
select * from t1_combined;
select * from t1_indices;
 
\echo ********************************************************************************
\echo * Vacuum table
\echo * - ctid (0,1) now reclaimed, index entries pointing to it removed
\echo * - ctid (0,5) reclaimed too, it never had index entries pointing to it
\echo
 
vacuum t1;
 
select * from t1_combined;
select * from t1_indices;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.