Debian Codegraph @

src/bin/scripts/t/100_vacuumdb.pl

raw
1
2 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
3
4 use strict;
5 use warnings FATAL => 'all';
6
7 use PostgreSQL::Test::Cluster;
8 use PostgreSQL::Test::Utils;
9 use Test::More;
10
11 program_help_ok('vacuumdb');
12 program_version_ok('vacuumdb');
13 program_options_handling_ok('vacuumdb');
14
15 my $node = PostgreSQL::Test::Cluster->new('main');
16 $node->init;
17 $node->start;
18
19 $node->issues_sql_like(
20 [ 'vacuumdb', 'postgres' ],
21 qr/statement: VACUUM.*;/,
22 'SQL VACUUM run');
23 $node->issues_sql_like(
24 [ 'vacuumdb', '-f', 'postgres' ],
25 qr/statement: VACUUM \(SKIP_DATABASE_STATS, FULL\).*;/,
26 'vacuumdb -f');
27 $node->issues_sql_like(
28 [ 'vacuumdb', '-F', 'postgres' ],
29 qr/statement: VACUUM \(SKIP_DATABASE_STATS, FREEZE\).*;/,
30 'vacuumdb -F');
31 $node->issues_sql_like(
32 [ 'vacuumdb', '-zj2', 'postgres' ],
33 qr/statement: VACUUM \(SKIP_DATABASE_STATS, ANALYZE\).*;/,
34 'vacuumdb -zj2');
35 $node->issues_sql_like(
36 [ 'vacuumdb', '-Z', 'postgres' ],
37 qr/statement: ANALYZE.*;/,
38 'vacuumdb -Z');
39 $node->issues_sql_like(
40 [ 'vacuumdb', '--disable-page-skipping', 'postgres' ],
41 qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING, SKIP_DATABASE_STATS\).*;/,
42 'vacuumdb --disable-page-skipping');
43 $node->issues_sql_like(
44 [ 'vacuumdb', '--skip-locked', 'postgres' ],
45 qr/statement: VACUUM \(SKIP_DATABASE_STATS, SKIP_LOCKED\).*;/,
46 'vacuumdb --skip-locked');
47 $node->issues_sql_like(
48 [ 'vacuumdb', '--skip-locked', '--analyze-only', 'postgres' ],
49 qr/statement: ANALYZE \(SKIP_LOCKED\).*;/,
50 'vacuumdb --skip-locked --analyze-only');
51 $node->command_fails(
52 [ 'vacuumdb', '--analyze-only', '--disable-page-skipping', 'postgres' ],
53 '--analyze-only and --disable-page-skipping specified together');
54 $node->issues_sql_like(
55 [ 'vacuumdb', '--no-index-cleanup', 'postgres' ],
56 qr/statement: VACUUM \(INDEX_CLEANUP FALSE, SKIP_DATABASE_STATS\).*;/,
57 'vacuumdb --no-index-cleanup');
58 $node->command_fails(
59 [ 'vacuumdb', '--analyze-only', '--no-index-cleanup', 'postgres' ],
60 '--analyze-only and --no-index-cleanup specified together');
61 $node->issues_sql_like(
62 [ 'vacuumdb', '--no-truncate', 'postgres' ],
63 qr/statement: VACUUM \(TRUNCATE FALSE, SKIP_DATABASE_STATS\).*;/,
64 'vacuumdb --no-truncate');
65 $node->command_fails(
66 [ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
67 '--analyze-only and --no-truncate specified together');
68 $node->issues_sql_like(
69 [ 'vacuumdb', '--no-process-main', 'postgres' ],
70 qr/statement: VACUUM \(PROCESS_MAIN FALSE, SKIP_DATABASE_STATS\).*;/,
71 'vacuumdb --no-process-main');
72 $node->command_fails(
73 [ 'vacuumdb', '--analyze-only', '--no-process-main', 'postgres' ],
74 '--analyze-only and --no-process-main specified together');
75 $node->issues_sql_like(
76 [ 'vacuumdb', '--no-process-toast', 'postgres' ],
77 qr/statement: VACUUM \(PROCESS_TOAST FALSE, SKIP_DATABASE_STATS\).*;/,
78 'vacuumdb --no-process-toast');
79 $node->command_fails(
80 [ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
81 '--analyze-only and --no-process-toast specified together');
82 $node->issues_sql_like(
83 [ 'vacuumdb', '--parallel' => 2, 'postgres' ],
84 qr/statement: VACUUM \(SKIP_DATABASE_STATS, PARALLEL 2\).*;/,
85 'vacuumdb -P 2');
86 $node->issues_sql_like(
87 [ 'vacuumdb', '--parallel' => 0, 'postgres' ],
88 qr/statement: VACUUM \(SKIP_DATABASE_STATS, PARALLEL 0\).*;/,
89 'vacuumdb -P 0');
90 $node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
91 'vacuumdb with connection string');
92
93 $node->command_fails(
94 [qw(vacuumdb -Zt pg_am;ABORT postgres)],
95 'trailing command in "-t", without COLUMNS');
96
97 # Unwanted; better if it failed.
98 $node->command_ok(
99 [qw(vacuumdb -Zt pg_am(amname);ABORT postgres)],
100 'trailing command in "-t", with COLUMNS');
101
102 $node->safe_psql(
103 'postgres', q|
104 CREATE TABLE "need""q(uot" (")x" text);
105 CREATE TABLE vactable (a int, b int);
106 CREATE VIEW vacview AS SELECT 1 as a;
107
108 CREATE FUNCTION f0(int) RETURNS int LANGUAGE SQL AS 'SELECT $1 * $1';
109 CREATE FUNCTION f1(int) RETURNS int LANGUAGE SQL AS 'SELECT f0($1)';
110 CREATE TABLE funcidx (x int);
111 INSERT INTO funcidx VALUES (0),(1),(2),(3);
112 CREATE SCHEMA "Foo";
113 CREATE TABLE "Foo".bar(id int);
114 CREATE SCHEMA "Bar";
115 CREATE TABLE "Bar".baz(id int);
116 |);
117 $node->command_ok([qw|vacuumdb -Z --table="need""q(uot"(")x") postgres|],
118 'column list');
119
120 $node->command_fails(
121 [ 'vacuumdb', '--analyze', '--table' => 'vactable(c)', 'postgres' ],
122 'incorrect column name with ANALYZE');
123 $node->command_fails([ 'vacuumdb', '--parallel' => -1, 'postgres' ],
124 'negative parallel degree');
125 $node->issues_sql_like(
126 [ 'vacuumdb', '--analyze', '--table' => 'vactable(a, b)', 'postgres' ],
127 qr/statement: VACUUM \(SKIP_DATABASE_STATS, ANALYZE\) public.vactable\(a, b\);/,
128 'vacuumdb --analyze with complete column list');
129 $node->issues_sql_like(
130 [ 'vacuumdb', '--analyze-only', '--table' => 'vactable(b)', 'postgres' ],
131 qr/statement: ANALYZE public.vactable\(b\);/,
132 'vacuumdb --analyze-only with partial column list');
133 $node->command_checks_all(
134 [ 'vacuumdb', '--analyze', '--table' => 'vacview', 'postgres' ],
135 0,
136 [qr/^.*vacuuming database "postgres"/],
137 [qr/^WARNING.*cannot vacuum non-tables or special system tables/s],
138 'vacuumdb with view');
139 $node->command_fails(
140 [
141 'vacuumdb',
142 '--table' => 'vactable',
143 '--min-mxid-age' => '0',
144 'postgres'
145 ],
146 'vacuumdb --min-mxid-age with incorrect value');
147 $node->command_fails(
148 [
149 'vacuumdb',
150 '--table' => 'vactable',
151 '--min-xid-age' => '0',
152 'postgres'
153 ],
154 'vacuumdb --min-xid-age with incorrect value');
155 $node->issues_sql_like(
156 [
157 'vacuumdb',
158 '--table' => 'vactable',
159 '--min-mxid-age' => '2147483000',
160 'postgres'
161 ],
162 qr/GREATEST.*relminmxid.*2147483000/,
163 'vacuumdb --table --min-mxid-age');
164 $node->issues_sql_like(
165 [ 'vacuumdb', '--min-xid-age' => '2147483001', 'postgres' ],
166 qr/GREATEST.*relfrozenxid.*2147483001/,
167 'vacuumdb --table --min-xid-age');
168 $node->issues_sql_like(
169 [ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
170 qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
171 'vacuumdb --schema');
172 $node->issues_sql_like(
173 [ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
174 qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
175 .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
176 /sx,
177 'vacuumdb multiple --schema switches');
178 $node->issues_sql_like(
179 [ 'vacuumdb', '--exclude-schema' => '"Foo"', 'postgres' ],
180 qr/^(?!.*VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar).*$/s,
181 'vacuumdb --exclude-schema');
182 $node->issues_sql_like(
183 [
184 'vacuumdb',
185 '--exclude-schema' => '"Foo"',
186 '--exclude-schema' => '"Bar"',
187 'postgres'
188 ],
189 qr/^(?!.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
190 | VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz).*$/sx,
191 'vacuumdb multiple --exclude-schema switches');
192 $node->command_fails_like(
193 [
194 'vacuumdb',
195 '--exclude-schema' => 'pg_catalog',
196 '--table' => 'pg_class',
197 'postgres',
198 ],
199 qr/cannot vacuum specific table\(s\) and exclude schema\(s\) at the same time/,
200 'cannot use options --exclude-schema and ---table at the same time');
201 $node->command_fails_like(
202 [
203 'vacuumdb',
204 '--schema' => 'pg_catalog',
205 '--table' => 'pg_class',
206 'postgres'
207 ],
208 qr/cannot vacuum all tables in schema\(s\) and specific table\(s\) at the same time/,
209 'cannot use options --schema and ---table at the same time');
210 $node->command_fails_like(
211 [
212 'vacuumdb',
213 '--schema' => 'pg_catalog',
214 '--exclude-schema' => '"Foo"',
215 'postgres'
216 ],
217 qr/cannot vacuum all tables in schema\(s\) and exclude schema\(s\) at the same time/,
218 'cannot use options --schema and --exclude-schema at the same time');
219 $node->issues_sql_like(
220 [ 'vacuumdb', '--all', '--exclude-schema' => 'pg_catalog' ],
221 qr/(?:(?!VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class).)*/,
222 'vacuumdb --all --exclude-schema');
223 $node->issues_sql_like(
224 [ 'vacuumdb', '--all', '--schema' => 'pg_catalog' ],
225 qr/VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class/,
226 'vacuumdb --all ---schema');
227 $node->issues_sql_like(
228 [ 'vacuumdb', '--all', '--table' => 'pg_class' ],
229 qr/VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class/,
230 'vacuumdb --all --table');
231 $node->command_fails_like(
232 [ 'vacuumdb', '--all', '-d' => 'postgres' ],
233 qr/cannot vacuum all databases and a specific one at the same time/,
234 'cannot use options --all and --dbname at the same time');
235 $node->command_fails_like(
236 [ 'vacuumdb', '--all', 'postgres' ],
237 qr/cannot vacuum all databases and a specific one at the same time/,
238 'cannot use option --all and a dbname as argument at the same time');
239
240 $node->safe_psql('postgres', q|
241 CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
242 ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
243 |);
244 $node->issues_sql_like(
245 [
246 'vacuumdb', '--analyze-only',
247 '--missing-stats-only', '-t',
248 'regression_vacuumdb_test', 'postgres'
249 ],
250 qr/statement:\ ANALYZE/sx,
251 '--missing-stats-only with missing stats');
252 $node->issues_sql_unlike(
253 [
254 'vacuumdb', '--analyze-only',
255 '--missing-stats-only', '-t',
256 'regression_vacuumdb_test', 'postgres'
257 ],
258 qr/statement:\ ANALYZE/sx,
259 '--missing-stats-only with no missing stats');
260
261 $node->safe_psql('postgres',
262 'CREATE INDEX regression_vacuumdb_test_idx ON regression_vacuumdb_test (mod(a, 2));'
263 );
264 $node->issues_sql_like(
265 [
266 'vacuumdb', '--analyze-in-stages',
267 '--missing-stats-only', '-t',
268 'regression_vacuumdb_test', 'postgres'
269 ],
270 qr/statement:\ ANALYZE/sx,
271 '--missing-stats-only with missing index expression stats');
272 $node->issues_sql_unlike(
273 [
274 'vacuumdb', '--analyze-in-stages',
275 '--missing-stats-only', '-t',
276 'regression_vacuumdb_test', 'postgres'
277 ],
278 qr/statement:\ ANALYZE/sx,
279 '--missing-stats-only with no missing index expression stats');
280
281 $node->safe_psql('postgres',
282 'CREATE STATISTICS regression_vacuumdb_test_stat ON a, b FROM regression_vacuumdb_test;'
283 );
284 $node->issues_sql_like(
285 [
286 'vacuumdb', '--analyze-only',
287 '--missing-stats-only', '-t',
288 'regression_vacuumdb_test', 'postgres'
289 ],
290 qr/statement:\ ANALYZE/sx,
291 '--missing-stats-only with missing extended stats');
292 $node->issues_sql_unlike(
293 [
294 'vacuumdb', '--analyze-only',
295 '--missing-stats-only', '-t',
296 'regression_vacuumdb_test', 'postgres'
297 ],
298 qr/statement:\ ANALYZE/sx,
299 '--missing-stats-only with no missing extended stats');
300
301 $node->safe_psql('postgres',
302 "CREATE TABLE regression_vacuumdb_child (a INT) INHERITS (regression_vacuumdb_test);\n"
303 . "INSERT INTO regression_vacuumdb_child VALUES (1, 2);\n"
304 . "ANALYZE regression_vacuumdb_child;\n");
305 $node->issues_sql_like(
306 [
307 'vacuumdb', '--analyze-in-stages',
308 '--missing-stats-only', '-t',
309 'regression_vacuumdb_test', 'postgres'
310 ],
311 qr/statement:\ ANALYZE/sx,
312 '--missing-stats-only with missing inherited stats');
313 $node->issues_sql_unlike(
314 [
315 'vacuumdb', '--analyze-in-stages',
316 '--missing-stats-only', '-t',
317 'regression_vacuumdb_test', 'postgres'
318 ],
319 qr/statement:\ ANALYZE/sx,
320 '--missing-stats-only with no missing inherited stats');
321
322 $node->safe_psql('postgres',
323 "CREATE TABLE regression_vacuumdb_parted (a INT) PARTITION BY LIST (a);\n"
324 . "CREATE TABLE regression_vacuumdb_part1 PARTITION OF regression_vacuumdb_parted FOR VALUES IN (1);\n"
325 . "CREATE INDEX ON regression_vacuumdb_parted ((a + 1));\n"
326 . "INSERT INTO regression_vacuumdb_parted VALUES (1);\n"
327 . "ANALYZE regression_vacuumdb_part1;\n");
328 $node->issues_sql_like(
329 [
330 'vacuumdb', '--analyze-only',
331 '--missing-stats-only', '-t',
332 'regression_vacuumdb_parted', 'postgres'
333 ],
334 qr/statement:\ ANALYZE/sx,
335 '--missing-stats-only with missing partition stats');
336 $node->issues_sql_unlike(
337 [
338 'vacuumdb', '--analyze-only',
339 '--missing-stats-only', '-t',
340 'regression_vacuumdb_parted', 'postgres'
341 ],
342 qr/statement:\ ANALYZE/sx,
343 '--missing-stats-only with no missing partition stats');
344
345 done_testing();