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
|
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);
insert into regular_table values (1), (2);
vacuum (disable_page_skipping) regular_table;
select count(*) > 0 from pg_visibility('regular_table');
?column?
----------
t
(1 row)
truncate regular_table;
select count(*) > 0 from pg_visibility('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)
-- 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;
|
