/
vacuum_gp.sql
256 lines (221 loc) · 9.14 KB
/
vacuum_gp.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
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
255
256
-- Test vacuum a catalog can succeed.
-- Previously, we have a redundant Assert in the function
-- localXidSatisfiesAnyDistributedSnapshot that will fail this.
-- See Github Issue 6793.
vacuum full pg_proc;
set optimizer_print_missing_stats = off;
-- MPP-23647 Create a partitioned appendonly table, let its age
-- increase during the test. We will vacuum it at the end of the
-- test.
create table ao_age_test (i int, b bool, c char, d date)
with (appendonly=true)
distributed by (i)
partition by list (b)
(partition b1 values ('f'),
partition b2 values ('t')
);
insert into ao_age_test select i, (i%123 > 50), (i/11) || '',
'2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
create index ao_age_test_i on ao_age_test(i);
-- MPP-23647 Create a empty table with no segments, let its age
-- increase during the test. We will vacuum it at the end of the
-- test.
create table ao_empty (a int, b int, c varchar)
with (appendonly=true) distributed by (a);
-- MPP-23647 Empty table with non-zero segments but each segment has
-- tupcount = 0. Also cover column oriented table.
create table aocs_empty (a int, b int, c varchar)
with (appendonly=true, orientation=column) distributed by (a);
insert into aocs_empty select i, i, i::text from generate_series(1,20)i;
-- This update should create a new appendonly segment.
update aocs_empty set c = 'updated' where a > 9;
delete from aocs_empty;
-- Make both the appendonly segments for this table empty. Let the
-- age of this table grow during the test. We will vacuum it again at
-- the end.
vacuum aocs_empty;
-- MPP-23647 Ensure pg_class.relhasindex for an empty appendonly table
-- is correctly interpreted by vacuum.
create index ao_empty_a on ao_empty(a);
create index aocs_empty_a on aocs_empty(a);
\d ao_empty
\d aocs_empty
-- vacuum/analyze a table with indexes
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
create index vactst_c on vactst (c);
vacuum vactst;
analyze vactst;
create index vactst_b on vactst using bitmap(b);
vacuum vactst;
analyze vactst;
vacuum analyze vactst;
drop table vactst;
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
create index vactst_c on vactst (c);
create index vactst_b on vactst using bitmap(b);
vacuum analyze vactst;
drop table vactst;
-- vacuum analyze a table that has dropped a column
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
alter table vactst drop column b;
vacuum analyze vactst;
alter table vactst drop column i;
vacuum analyze vactst;
drop table vactst;
-- vacuum analyze a table whose index has pg_statistic stats
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
create index vactst_c on vactst (upper(c));
vacuum analyze vactst;
drop table vactst;
-- vacuum analyze an AO table
create table vactst (i int, b bool, c char, d date) with (appendonly=true);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
vacuum analyze vactst;
drop table vactst;
-- vacuum analyze a partition table
create table vactst (i int, b bool, c char, d date)
distributed by (i)
partition by list (b)
(partition b1 values ('f'),
partition b2 values ('t')
);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
vacuum analyze vactst;
drop table vactst;
-- MPP-23647 Vacuum appendonly partitioned table ao_age_test, check
-- that its age is correctly updated along with its partitions.
set vacuum_freeze_min_age=20;
show vacuum_freeze_min_age;
select relname from pg_class where relname like 'ao_age_test%'
order by relname;
vacuum ao_age_test;
-- Note: in checking age() below, be mindful of not checking absolute
-- age value in expected output so as to make the test reliable.
-- Assuming no other activity, vacuum needs one transaction ID for
-- each of the three tables.
-- AO/CO tables should have relfrozenxid = 0.
select relname, relfrozenxid from pg_class
where relname like 'ao_age_test%' and relkind = 'r' order by 1;
-- Vacuum the other two empty tables and verify the age of auxiliary tables is
-- updated correctly.
vacuum ao_empty;
vacuum aocs_empty;
-- AO/CO tables should have relfrozenxid = 0.
select relname, relfrozenxid from pg_class
where relname in ('ao_empty', 'aocs_empty') order by 1;
select * from ao_empty;
select * from aocs_empty;
-- Verify that age of appendonly auxiliary tables is update by vacuum.
select 0 < age(relfrozenxid) as age_positive,
age(relfrozenxid) < 100 as age_within_limit
from pg_class c, pg_appendonly a
where c.oid = a.segrelid and
(a.relid = 'ao_empty'::regclass or
a.relid = 'aocs_empty'::regclass);
-- Verify that age of toast table is updated by vacuum.
select 0 < age(relfrozenxid) as age_positive,
age(relfrozenxid) < 100 as age_within_limit
from pg_class where oid in (select reltoastrelid from pg_class
where relname = 'ao_empty' or relname = 'aocs_empty');
-- Verify that index is displayed by \d after vacuum.
\d ao_empty;
\d aocs_empty;
-- Ensure that reindex after vacuum works fine.
alter table ao_age_test set with (reorganize='true')
distributed by (c);
-- Force an index scan and verify index lookup work fine after vacuum
-- and reorganize.
set enable_seqscan = false;
select * from ao_age_test where i in (1, 2, 11, 13, 15)
order by i;
drop table ao_age_test;
drop table ao_empty;
drop table aocs_empty;
-- Create some dead tuples, and test VACUUM FULL on the AO/CO auxiliary
-- relations. (There used to be a bug where it didn't do anything, and you got a
-- NOTICE saying a transaction is in progress instead.)
create table co_t1(a int, b int) with (appendonly=true, orientation=column) distributed by(a);
insert into co_t1 select i, i from generate_series(1, 10000) i;
update co_t1 set b = b + 1;
vacuum full co_t1;
drop table co_t1;
create table ao_t1(a int, b int) with (appendonly=true) distributed by(a);
insert into ao_t1 select i, i from generate_series(1, 10000) i;
update ao_t1 set b = b + 1;
vacuum full ao_t1;
drop table ao_t1;
vacuum full gp_persistent_relation_node;
-- superuser must be able to vacuum analyze the table
DROP ROLE IF EXISTS r_priv_test;
CREATE ROLE r_priv_test;
CREATE SCHEMA s_priv_test;
CREATE TABLE s_priv_test.t_priv_table(a INT);
INSERT INTO s_priv_test.t_priv_table SELECT i FROM generate_series(1, 10)i;
ALTER TABLE s_priv_test.t_priv_table OWNER TO r_priv_test;
VACUUM ANALYZE s_priv_test.t_priv_table;
DROP SCHEMA s_priv_test CASCADE;
DROP ROLE r_priv_test;
-- Ensure that VACUUM doesn't reset the statistics on the parent table
CREATE TABLE pt (a int, b int) DISTRIBUTED BY (a) PARTITION BY range (b) (END(5), START(5));
INSERT INTO pt SELECT 0, 6 FROM generate_series(1, 12);
ANALYZE pt;
SELECT reltuples FROM pg_catalog.pg_class WHERE relname = 'pt';
VACUUM pt;
SELECT reltuples FROM pg_catalog.pg_class WHERE relname = 'pt';
VACUUM ANALYZE pt;
SELECT reltuples FROM pg_catalog.pg_class WHERE relname = 'pt';
-- Vacuum freeze for database with toast attribute in pg_database tuple cause
-- heap_inplace_update raise error "wrong tuple length". This is because system
-- cache flatten toast tuple.
DROP DATABASE IF EXISTS vacuum_freeze_test;
CREATE DATABASE vacuum_freeze_test;
-- start_ignore
create or replace function toast_pg_database_datacl() returns text as $body$
declare
mycounter int;
begin
for mycounter in select i from generate_series(1, 2800) i loop
execute 'create role aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || mycounter;
execute 'grant ALL on database vacuum_freeze_test to aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || mycounter;
end loop;
return 'ok';
end;
$body$ language plpgsql volatile strict;
create or replace function clean_roles() returns text as $body$
declare
mycounter int;
begin
for mycounter in select i from generate_series(1, 2800) i loop
execute 'drop role aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || mycounter;
end loop;
return 'ok';
end;
$body$ language plpgsql volatile strict;
select toast_pg_database_datacl();
-- end_ignore
\c vacuum_freeze_test
create temp table before_vacuum as select datname, pg_column_size(datacl) > 8192 as datacl_size, age(datfrozenxid) from pg_database where datname='vacuum_freeze_test';
select datname, datacl_size from before_vacuum;
vacuum freeze;
select datname, pg_column_size(datacl) > 8192 as datacl_size, age(datfrozenxid) != (select age from before_vacuum) as age_changed from pg_database where datname='vacuum_freeze_test';
\c regression
DROP DATABASE vacuum_freeze_test;
-- start_ignore
select clean_roles();
drop function toast_pg_database_datacl();
drop function clean_roles();
-- end_ignore
-- free pg_global space, otherwise it fails db_size_functions
VACUUM FULL pg_authid;
VACUUM FULL pg_database;