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

   1
   2 # Copyright (c) 2021-2024, 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', '-P', 2, 'postgres' ],
  84         qr/statement: VACUUM \(SKIP_DATABASE_STATS, PARALLEL 2\).*;/,
  85         'vacuumdb -P 2');
  86 $node->issues_sql_like(
  87         [ 'vacuumdb', '-P', 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', '-P', -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         [ 'vacuumdb', '--table', 'vactable', '--min-mxid-age', '0', 'postgres' ],
 141         'vacuumdb --min-mxid-age with incorrect value');
 142 $node->command_fails(
 143         [ 'vacuumdb', '--table', 'vactable', '--min-xid-age', '0', 'postgres' ],
 144         'vacuumdb --min-xid-age with incorrect value');
 145 $node->issues_sql_like(
 146         [
 147                 'vacuumdb', '--table', 'vactable', '--min-mxid-age',
 148                 '2147483000', 'postgres'
 149         ],
 150         qr/GREATEST.*relminmxid.*2147483000/,
 151         'vacuumdb --table --min-mxid-age');
 152 $node->issues_sql_like(
 153         [ 'vacuumdb', '--min-xid-age', '2147483001', 'postgres' ],
 154         qr/GREATEST.*relfrozenxid.*2147483001/,
 155         'vacuumdb --table --min-xid-age');
 156 $node->issues_sql_like(
 157         [ 'vacuumdb', '--schema', '"Foo"', 'postgres' ],
 158         qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
 159         'vacuumdb --schema');
 160 $node->issues_sql_like(
 161         [ 'vacuumdb', '--schema', '"Foo"', '--schema', '"Bar"', 'postgres' ],
 162         qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
 163                 .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
 164         /sx,
 165         'vacuumdb multiple --schema switches');
 166 $node->issues_sql_like(
 167         [ 'vacuumdb', '--exclude-schema', '"Foo"', 'postgres' ],
 168         qr/^(?!.*VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar).*$/s,
 169         'vacuumdb --exclude-schema');
 170 $node->issues_sql_like(
 171         [
 172                 'vacuumdb', '--exclude-schema', '"Foo"', '--exclude-schema',
 173                 '"Bar"', 'postgres'
 174         ],
 175         qr/^(?!.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
 176         | VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz).*$/sx,
 177         'vacuumdb multiple --exclude-schema switches');
 178 $node->command_fails_like(
 179         [ 'vacuumdb', '-N', 'pg_catalog', '-t', 'pg_class', 'postgres', ],
 180         qr/cannot vacuum specific table\(s\) and exclude schema\(s\) at the same time/,
 181         'cannot use options -N and -t at the same time');
 182 $node->command_fails_like(
 183         [ 'vacuumdb', '-n', 'pg_catalog', '-t', 'pg_class', 'postgres' ],
 184         qr/cannot vacuum all tables in schema\(s\) and specific table\(s\) at the same time/,
 185         'cannot use options -n and -t at the same time');
 186 $node->command_fails_like(
 187         [ 'vacuumdb', '-n', 'pg_catalog', '-N', '"Foo"', 'postgres' ],
 188         qr/cannot vacuum all tables in schema\(s\) and exclude schema\(s\) at the same time/,
 189         'cannot use options -n and -N at the same time');
 190 $node->issues_sql_like(
 191         [ 'vacuumdb', '-a', '-N', 'pg_catalog' ],
 192         qr/(?:(?!VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class).)*/,
 193         'vacuumdb -a -N');
 194 $node->issues_sql_like(
 195         [ 'vacuumdb', '-a', '-n', 'pg_catalog' ],
 196         qr/VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class/,
 197         'vacuumdb -a -n');
 198 $node->issues_sql_like(
 199         [ 'vacuumdb', '-a', '-t', 'pg_class' ],
 200         qr/VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class/,
 201         'vacuumdb -a -t');
 202 $node->command_fails_like(
 203         [ 'vacuumdb', '-a', '-d', 'postgres' ],
 204         qr/cannot vacuum all databases and a specific one at the same time/,
 205         'cannot use options -a and -d at the same time');
 206 $node->command_fails_like(
 207         [ 'vacuumdb', '-a', 'postgres' ],
 208         qr/cannot vacuum all databases and a specific one at the same time/,
 209         'cannot use option -a and a dbname as argument at the same time');
 210
 211 done_testing();