1create extension pg_surgery; 2-- create a normal heap table and insert some rows. 3-- use a temp table so that vacuum behavior doesn't depend on global xmin 4create temp table htab (a int); 5insert into htab values (100), (200), (300), (400), (500); 6-- test empty TID array 7select heap_force_freeze('htab'::regclass, ARRAY[]::tid[]); 8 heap_force_freeze 9------------------- 10 11(1 row) 12 13-- nothing should be frozen yet 14select * from htab where xmin = 2; 15 a 16--- 17(0 rows) 18 19-- freeze forcibly 20select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]); 21 heap_force_freeze 22------------------- 23 24(1 row) 25 26-- now we should have one frozen tuple 27select ctid, xmax from htab where xmin = 2; 28 ctid | xmax 29-------+------ 30 (0,4) | 0 31(1 row) 32 33-- kill forcibly 34select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]); 35 heap_force_kill 36----------------- 37 38(1 row) 39 40-- should be gone now 41select * from htab where ctid = '(0, 4)'; 42 a 43--- 44(0 rows) 45 46-- should now be skipped because it's already dead 47select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]); 48NOTICE: skipping tid (0, 4) for relation "htab" because it is marked dead 49 heap_force_kill 50----------------- 51 52(1 row) 53 54select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]); 55NOTICE: skipping tid (0, 4) for relation "htab" because it is marked dead 56 heap_force_freeze 57------------------- 58 59(1 row) 60 61-- freeze two TIDs at once while skipping an out-of-range block number 62select heap_force_freeze('htab'::regclass, 63 ARRAY['(0, 1)', '(0, 3)', '(1, 1)']::tid[]); 64NOTICE: skipping block 1 for relation "htab" because the block number is out of range 65 heap_force_freeze 66------------------- 67 68(1 row) 69 70-- we should now have two frozen tuples 71select ctid, xmax from htab where xmin = 2; 72 ctid | xmax 73-------+------ 74 (0,1) | 0 75 (0,3) | 0 76(2 rows) 77 78-- out-of-range TIDs should be skipped 79select heap_force_freeze('htab'::regclass, ARRAY['(0, 0)', '(0, 6)']::tid[]); 80NOTICE: skipping tid (0, 0) for relation "htab" because the item number is out of range 81NOTICE: skipping tid (0, 6) for relation "htab" because the item number is out of range 82 heap_force_freeze 83------------------- 84 85(1 row) 86 87-- set up a new table with a redirected line pointer 88-- use a temp table so that vacuum behavior doesn't depend on global xmin 89create temp table htab2(a int); 90insert into htab2 values (100); 91update htab2 set a = 200; 92vacuum htab2; 93-- redirected TIDs should be skipped 94select heap_force_kill('htab2'::regclass, ARRAY['(0, 1)']::tid[]); 95NOTICE: skipping tid (0, 1) for relation "htab2" because it redirects to item 2 96 heap_force_kill 97----------------- 98 99(1 row) 100 101-- now create an unused line pointer 102select ctid from htab2; 103 ctid 104------- 105 (0,2) 106(1 row) 107 108update htab2 set a = 300; 109select ctid from htab2; 110 ctid 111------- 112 (0,3) 113(1 row) 114 115vacuum freeze htab2; 116-- unused TIDs should be skipped 117select heap_force_kill('htab2'::regclass, ARRAY['(0, 2)']::tid[]); 118NOTICE: skipping tid (0, 2) for relation "htab2" because it is marked unused 119 heap_force_kill 120----------------- 121 122(1 row) 123 124-- multidimensional TID array should be rejected 125select heap_force_kill('htab2'::regclass, ARRAY[['(0, 2)']]::tid[]); 126ERROR: argument must be empty or one-dimensional array 127-- TID array with nulls should be rejected 128select heap_force_kill('htab2'::regclass, ARRAY[NULL]::tid[]); 129ERROR: array must not contain nulls 130-- but we should be able to kill the one tuple we have 131select heap_force_kill('htab2'::regclass, ARRAY['(0, 3)']::tid[]); 132 heap_force_kill 133----------------- 134 135(1 row) 136 137-- materialized view. 138-- note that we don't commit the transaction, so autovacuum can't interfere. 139begin; 140create materialized view mvw as select a from generate_series(1, 3) a; 141select * from mvw where xmin = 2; 142 a 143--- 144(0 rows) 145 146select heap_force_freeze('mvw'::regclass, ARRAY['(0, 3)']::tid[]); 147 heap_force_freeze 148------------------- 149 150(1 row) 151 152select * from mvw where xmin = 2; 153 a 154--- 155 3 156(1 row) 157 158select heap_force_kill('mvw'::regclass, ARRAY['(0, 3)']::tid[]); 159 heap_force_kill 160----------------- 161 162(1 row) 163 164select * from mvw where ctid = '(0, 3)'; 165 a 166--- 167(0 rows) 168 169rollback; 170-- check that it fails on an unsupported relkind 171create view vw as select 1; 172select heap_force_kill('vw'::regclass, ARRAY['(0, 1)']::tid[]); 173ERROR: "vw" is not a table, materialized view, or TOAST table 174select heap_force_freeze('vw'::regclass, ARRAY['(0, 1)']::tid[]); 175ERROR: "vw" is not a table, materialized view, or TOAST table 176-- cleanup. 177drop view vw; 178drop extension pg_surgery; 179