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

   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 --excludes-chema 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 done_testing();