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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
|
CREATE EXTENSION pg_visibility;
--
-- recently-dropped table
--
\set VERBOSITY sqlstate
BEGIN;
CREATE TABLE droppedtest (c int);
SELECT 'droppedtest'::regclass::oid AS oid \gset
SAVEPOINT q; DROP TABLE droppedtest; RELEASE q;
SAVEPOINT q; SELECT * FROM pg_visibility_map(:oid); ROLLBACK TO q;
ERROR: XX000
-- ERROR: could not open relation with OID 16xxx
SAVEPOINT q; SELECT 1; ROLLBACK TO q;
?column?
----------
1
(1 row)
SAVEPOINT q; SELECT 1; ROLLBACK TO q;
?column?
----------
1
(1 row)
SELECT pg_relation_size(:oid), pg_relation_filepath(:oid),
has_table_privilege(:oid, 'SELECT');
pg_relation_size | pg_relation_filepath | has_table_privilege
------------------+----------------------+---------------------
| |
(1 row)
SELECT * FROM pg_visibility_map(:oid);
ERROR: XX000
-- ERROR: could not open relation with OID 16xxx
ROLLBACK;
\set VERBOSITY default
--
-- check that using the module's functions with unsupported relations will fail
--
-- partitioned tables (the parent ones) don't have visibility maps
create table test_partitioned (a int) partition by list (a);
-- these should all fail
select pg_visibility('test_partitioned', 0);
ERROR: "test_partitioned" is not a table, materialized view, or TOAST table
select pg_visibility_map('test_partitioned');
ERROR: "test_partitioned" is not a table, materialized view, or TOAST table
select pg_visibility_map_summary('test_partitioned');
ERROR: "test_partitioned" is not a table, materialized view, or TOAST table
select pg_check_frozen('test_partitioned');
ERROR: "test_partitioned" is not a table, materialized view, or TOAST table
select pg_truncate_visibility_map('test_partitioned');
ERROR: "test_partitioned" is not a table, materialized view, or TOAST table
create table test_partition partition of test_partitioned for values in (1);
create index test_index on test_partition (a);
-- indexes do not, so these all fail
select pg_visibility('test_index', 0);
ERROR: "test_index" is not a table, materialized view, or TOAST table
select pg_visibility_map('test_index');
ERROR: "test_index" is not a table, materialized view, or TOAST table
select pg_visibility_map_summary('test_index');
ERROR: "test_index" is not a table, materialized view, or TOAST table
select pg_check_frozen('test_index');
ERROR: "test_index" is not a table, materialized view, or TOAST table
select pg_truncate_visibility_map('test_index');
ERROR: "test_index" is not a table, materialized view, or TOAST table
create view test_view as select 1;
-- views do not have VMs, so these all fail
select pg_visibility('test_view', 0);
ERROR: "test_view" is not a table, materialized view, or TOAST table
select pg_visibility_map('test_view');
ERROR: "test_view" is not a table, materialized view, or TOAST table
select pg_visibility_map_summary('test_view');
ERROR: "test_view" is not a table, materialized view, or TOAST table
select pg_check_frozen('test_view');
ERROR: "test_view" is not a table, materialized view, or TOAST table
select pg_truncate_visibility_map('test_view');
ERROR: "test_view" is not a table, materialized view, or TOAST table
create sequence test_sequence;
-- sequences do not have VMs, so these all fail
select pg_visibility('test_sequence', 0);
ERROR: "test_sequence" is not a table, materialized view, or TOAST table
select pg_visibility_map('test_sequence');
ERROR: "test_sequence" is not a table, materialized view, or TOAST table
select pg_visibility_map_summary('test_sequence');
ERROR: "test_sequence" is not a table, materialized view, or TOAST table
select pg_check_frozen('test_sequence');
ERROR: "test_sequence" is not a table, materialized view, or TOAST table
select pg_truncate_visibility_map('test_sequence');
ERROR: "test_sequence" is not a table, materialized view, or TOAST table
create foreign data wrapper dummy;
create server dummy_server foreign data wrapper dummy;
create foreign table test_foreign_table () server dummy_server;
-- foreign tables do not have VMs, so these all fail
select pg_visibility('test_foreign_table', 0);
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
select pg_visibility_map('test_foreign_table');
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
select pg_visibility_map_summary('test_foreign_table');
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
select pg_check_frozen('test_foreign_table');
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
select pg_truncate_visibility_map('test_foreign_table');
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
-- check some of the allowed relkinds
create table regular_table (a int, b text);
alter table regular_table alter column b set storage external;
insert into regular_table values (1, repeat('one', 1000)), (2, repeat('two', 1000));
vacuum (disable_page_skipping) regular_table;
select count(*) > 0 from pg_visibility('regular_table');
?column?
----------
t
(1 row)
select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class where relname = 'regular_table'));
?column?
----------
t
(1 row)
truncate regular_table;
select count(*) > 0 from pg_visibility('regular_table');
?column?
----------
f
(1 row)
select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class where relname = 'regular_table'));
?column?
----------
f
(1 row)
create materialized view matview_visibility_test as select * from regular_table;
vacuum (disable_page_skipping) matview_visibility_test;
select count(*) > 0 from pg_visibility('matview_visibility_test');
?column?
----------
f
(1 row)
insert into regular_table values (1), (2);
refresh materialized view matview_visibility_test;
select count(*) > 0 from pg_visibility('matview_visibility_test');
?column?
----------
t
(1 row)
-- regular tables which are part of a partition *do* have visibility maps
insert into test_partition values (1);
vacuum (disable_page_skipping) test_partition;
select count(*) > 0 from pg_visibility('test_partition', 0);
?column?
----------
t
(1 row)
select count(*) > 0 from pg_visibility_map('test_partition');
?column?
----------
t
(1 row)
select count(*) > 0 from pg_visibility_map_summary('test_partition');
?column?
----------
t
(1 row)
select * from pg_check_frozen('test_partition'); -- hopefully none
t_ctid
--------
(0 rows)
select pg_truncate_visibility_map('test_partition');
pg_truncate_visibility_map
----------------------------
(1 row)
-- test copy freeze
create table copyfreeze (a int, b char(1500));
-- load all rows via COPY FREEZE and ensure that all pages are set all-visible
-- and all-frozen.
begin;
truncate copyfreeze;
copy copyfreeze from stdin freeze;
commit;
select * from pg_visibility_map('copyfreeze');
blkno | all_visible | all_frozen
-------+-------------+------------
0 | t | t
1 | t | t
2 | t | t
(3 rows)
select * from pg_check_frozen('copyfreeze');
t_ctid
--------
(0 rows)
-- load half the rows via regular COPY and rest via COPY FREEZE. The pages
-- which are touched by regular COPY must not be set all-visible/all-frozen. On
-- the other hand, pages allocated by COPY FREEZE should be marked
-- all-frozen/all-visible.
begin;
truncate copyfreeze;
copy copyfreeze from stdin;
copy copyfreeze from stdin freeze;
commit;
select * from pg_visibility_map('copyfreeze');
blkno | all_visible | all_frozen
-------+-------------+------------
0 | f | f
1 | f | f
2 | t | t
(3 rows)
select * from pg_check_frozen('copyfreeze');
t_ctid
--------
(0 rows)
-- Try a mix of regular COPY and COPY FREEZE.
begin;
truncate copyfreeze;
copy copyfreeze from stdin freeze;
copy copyfreeze from stdin;
copy copyfreeze from stdin freeze;
commit;
select * from pg_visibility_map('copyfreeze');
blkno | all_visible | all_frozen
-------+-------------+------------
0 | t | t
1 | f | f
2 | t | t
(3 rows)
select * from pg_check_frozen('copyfreeze');
t_ctid
--------
(0 rows)
-- cleanup
drop table test_partitioned;
drop view test_view;
drop sequence test_sequence;
drop foreign table test_foreign_table;
drop server dummy_server;
drop foreign data wrapper dummy;
drop materialized view matview_visibility_test;
drop table regular_table;
drop table copyfreeze;
|
