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