src/bin/scripts/t/090_reindexdb.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('reindexdb');
  12 program_version_ok('reindexdb');
  13 program_options_handling_ok('reindexdb');
  14
  15 my $node = PostgreSQL::Test::Cluster->new('main');
  16 $node->init;
  17 $node->start;
  18
  19 $ENV{PGOPTIONS} = '--client-min-messages=WARNING';
  20
  21 # Create a tablespace for testing.
  22 my $tbspace_path = $node->basedir . '/regress_reindex_tbspace';
  23 mkdir $tbspace_path or die "cannot create directory $tbspace_path";
  24 my $tbspace_name = 'reindex_tbspace';
  25 $node->safe_psql('postgres',
  26         "CREATE TABLESPACE $tbspace_name LOCATION '$tbspace_path';");
  27
  28 # Use text as data type to get a toast table.
  29 $node->safe_psql('postgres',
  30         'CREATE TABLE test1 (a text); CREATE INDEX test1x ON test1 (a);');
  31 # Collect toast table and index names of this relation, for later use.
  32 my $toast_table = $node->safe_psql('postgres',
  33         "SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'test1'::regclass;"
  34 );
  35 my $toast_index = $node->safe_psql('postgres',
  36         "SELECT indexrelid::regclass FROM pg_index WHERE indrelid = '$toast_table'::regclass;"
  37 );
  38
  39 # Set of SQL queries to cross-check the state of relfilenodes across
  40 # REINDEX operations.  A set of relfilenodes is saved from the catalogs
  41 # and then compared with pg_class.
  42 $node->safe_psql('postgres',
  43         'CREATE TABLE index_relfilenodes (parent regclass, indname text, indoid oid, relfilenode oid);'
  44 );
  45 # Save the relfilenode of a set of toast indexes, one from the catalog
  46 # pg_constraint and one from the test table.
  47 my $fetch_toast_relfilenodes =
  48   qq{SELECT b.oid::regclass, c.oid::regclass::text, c.oid, c.relfilenode
  49   FROM pg_class a
  50     JOIN pg_class b ON (a.oid = b.reltoastrelid)
  51     JOIN pg_index i on (a.oid = i.indrelid)
  52     JOIN pg_class c on (i.indexrelid = c.oid)
  53   WHERE b.oid IN ('pg_constraint'::regclass, 'test1'::regclass)};
  54 # Same for relfilenodes of normal indexes.  This saves the relfilenode
  55 # from an index of pg_constraint, and from the index of the test table.
  56 my $fetch_index_relfilenodes =
  57   qq{SELECT i.indrelid, a.oid::regclass::text, a.oid, a.relfilenode
  58   FROM pg_class a
  59     JOIN pg_index i ON (i.indexrelid = a.oid)
  60   WHERE a.relname IN ('pg_constraint_oid_index', 'test1x')};
  61 my $save_relfilenodes =
  62         "INSERT INTO index_relfilenodes $fetch_toast_relfilenodes;"
  63   . "INSERT INTO index_relfilenodes $fetch_index_relfilenodes;";
  64
  65 # Query to compare a set of relfilenodes saved with the contents of pg_class.
  66 # Note that this does not join using OIDs, as CONCURRENTLY would change them
  67 # when reindexing.  A filter is applied on the toast index names, even if this
  68 # does not make a difference between the catalog and normal ones.  The ordering
  69 # based on the name is enough to ensure a fixed output, where the name of the
  70 # parent table is included to provide more context.
  71 my $compare_relfilenodes = qq(SELECT b.parent::regclass,
  72   regexp_replace(b.indname::text, '(pg_toast.pg_toast_)\\d+(_index)', '\\1<oid>\\2'),
  73   CASE WHEN a.oid = b.indoid THEN 'OID is unchanged'
  74     ELSE 'OID has changed' END,
  75   CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
  76     ELSE 'relfilenode has changed' END
  77   FROM index_relfilenodes b
  78     JOIN pg_class a ON b.indname::text = a.oid::regclass::text
  79   ORDER BY b.parent::text, b.indname::text);
  80
  81 # Save the set of relfilenodes and compare them.
  82 $node->safe_psql('postgres', $save_relfilenodes);
  83 $node->issues_sql_like(
  84         [ 'reindexdb', 'postgres' ],
  85         qr/statement: REINDEX DATABASE postgres;/,
  86         'SQL REINDEX run');
  87 my $relnode_info = $node->safe_psql('postgres', $compare_relfilenodes);
  88 is( $relnode_info,
  89         qq(pg_constraint|pg_constraint_oid_index|OID is unchanged|relfilenode is unchanged
  90 pg_constraint|pg_toast.pg_toast_<oid>_index|OID is unchanged|relfilenode is unchanged
  91 test1|pg_toast.pg_toast_<oid>_index|OID is unchanged|relfilenode has changed
  92 test1|test1x|OID is unchanged|relfilenode has changed),
  93         'relfilenode change after REINDEX DATABASE');
  94
  95 # Re-save and run the second one.
  96 $node->safe_psql('postgres',
  97         "TRUNCATE index_relfilenodes; $save_relfilenodes");
  98 $node->issues_sql_like(
  99         [ 'reindexdb', '--system', 'postgres' ],
 100         qr/statement: REINDEX SYSTEM postgres;/,
 101         'reindex system tables');
 102 $relnode_info = $node->safe_psql('postgres', $compare_relfilenodes);
 103 is( $relnode_info,
 104         qq(pg_constraint|pg_constraint_oid_index|OID is unchanged|relfilenode has changed
 105 pg_constraint|pg_toast.pg_toast_<oid>_index|OID is unchanged|relfilenode has changed
 106 test1|pg_toast.pg_toast_<oid>_index|OID is unchanged|relfilenode is unchanged
 107 test1|test1x|OID is unchanged|relfilenode is unchanged),
 108         'relfilenode change after REINDEX SYSTEM');
 109
 110 $node->issues_sql_like(
 111         [ 'reindexdb', '--table' => 'test1', 'postgres' ],
 112         qr/statement: REINDEX TABLE public\.test1;/,
 113         'reindex specific table');
 114 $node->issues_sql_like(
 115         [
 116                 'reindexdb',
 117                 '--table' => 'test1',
 118                 '--tablespace' => $tbspace_name,
 119                 'postgres',
 120         ],
 121         qr/statement: REINDEX \(TABLESPACE $tbspace_name\) TABLE public\.test1;/,
 122         'reindex specific table on tablespace');
 123 $node->issues_sql_like(
 124         [ 'reindexdb', '--index' => 'test1x', 'postgres' ],
 125         qr/statement: REINDEX INDEX public\.test1x;/,
 126         'reindex specific index');
 127 $node->issues_sql_like(
 128         [ 'reindexdb', '--schema' => 'pg_catalog', 'postgres' ],
 129         qr/statement: REINDEX SCHEMA pg_catalog;/,
 130         'reindex specific schema');
 131 $node->issues_sql_like(
 132         [ 'reindexdb', '--verbose', '--table' => 'test1', 'postgres' ],
 133         qr/statement: REINDEX \(VERBOSE\) TABLE public\.test1;/,
 134         'reindex with verbose output');
 135 $node->issues_sql_like(
 136         [
 137                 'reindexdb',
 138                 '--verbose',
 139                 '--table' => 'test1',
 140                 '--tablespace' => $tbspace_name,
 141                 'postgres',
 142         ],
 143         qr/statement: REINDEX \(VERBOSE, TABLESPACE $tbspace_name\) TABLE public\.test1;/,
 144         'reindex with verbose output and tablespace');
 145
 146 # Same with --concurrently.
 147 # Save the state of the relations and compare them after the DATABASE
 148 # rebuild.
 149 $node->safe_psql('postgres',
 150         "TRUNCATE index_relfilenodes; $save_relfilenodes");
 151 $node->issues_sql_like(
 152         [ 'reindexdb', '--concurrently', 'postgres' ],
 153         qr/statement: REINDEX DATABASE CONCURRENTLY postgres;/,
 154         'SQL REINDEX CONCURRENTLY run');
 155 $relnode_info = $node->safe_psql('postgres', $compare_relfilenodes);
 156 is( $relnode_info,
 157         qq(pg_constraint|pg_constraint_oid_index|OID is unchanged|relfilenode is unchanged
 158 pg_constraint|pg_toast.pg_toast_<oid>_index|OID is unchanged|relfilenode is unchanged
 159 test1|pg_toast.pg_toast_<oid>_index|OID has changed|relfilenode has changed
 160 test1|test1x|OID has changed|relfilenode has changed),
 161         'OID change after REINDEX DATABASE CONCURRENTLY');
 162
 163 $node->issues_sql_like(
 164         [ 'reindexdb', '--concurrently', '--table' => 'test1', 'postgres' ],
 165         qr/statement: REINDEX TABLE CONCURRENTLY public\.test1;/,
 166         'reindex specific table concurrently');
 167 $node->issues_sql_like(
 168         [ 'reindexdb', '--concurrently', '--index' => 'test1x', 'postgres' ],
 169         qr/statement: REINDEX INDEX CONCURRENTLY public\.test1x;/,
 170         'reindex specific index concurrently');
 171 $node->issues_sql_like(
 172         [ 'reindexdb', '--concurrently', '--schema' => 'public', 'postgres' ],
 173         qr/statement: REINDEX SCHEMA CONCURRENTLY public;/,
 174         'reindex specific schema concurrently');
 175 $node->command_fails(
 176         [ 'reindexdb', '--concurrently', '--system', 'postgres' ],
 177         'reindex system tables concurrently');
 178 $node->issues_sql_like(
 179         [
 180                 'reindexdb', '--concurrently', '--verbose',
 181                 '--table' => 'test1',
 182                 'postgres',
 183         ],
 184         qr/statement: REINDEX \(VERBOSE\) TABLE CONCURRENTLY public\.test1;/,
 185         'reindex with verbose output concurrently');
 186 $node->issues_sql_like(
 187         [
 188                 'reindexdb',
 189                 '--concurrently',
 190                 '--verbose',
 191                 '--table' => 'test1',
 192                 '--tablespace' => $tbspace_name,
 193                 'postgres',
 194         ],
 195         qr/statement: REINDEX \(VERBOSE, TABLESPACE $tbspace_name\) TABLE CONCURRENTLY public\.test1;/,
 196         'reindex concurrently with verbose output and tablespace');
 197
 198 # REINDEX TABLESPACE on toast indexes and tables fails.  This is not
 199 # part of the main regression test suite as these have unpredictable
 200 # names, and CONCURRENTLY cannot be used in transaction blocks, preventing
 201 # the use of TRY/CATCH blocks in a custom function to filter error
 202 # messages.
 203 $node->command_checks_all(
 204         [
 205                 'reindexdb',
 206                 '--table' => $toast_table,
 207                 '--tablespace' => $tbspace_name,
 208                 'postgres',
 209         ],
 210         1,
 211         [],
 212         [qr/cannot move system relation/],
 213         'reindex toast table with tablespace');
 214 $node->command_checks_all(
 215         [
 216                 'reindexdb',
 217                 '--concurrently',
 218                 '--table' => $toast_table,
 219                 '--tablespace' => $tbspace_name,
 220                 'postgres',
 221         ],
 222         1,
 223         [],
 224         [qr/cannot move system relation/],
 225         'reindex toast table concurrently with tablespace');
 226 $node->command_checks_all(
 227         [
 228                 'reindexdb',
 229                 '--index' => $toast_index,
 230                 '--tablespace' => $tbspace_name,
 231                 'postgres',
 232         ],
 233         1,
 234         [],
 235         [qr/cannot move system relation/],
 236         'reindex toast index with tablespace');
 237 $node->command_checks_all(
 238         [
 239                 'reindexdb',
 240                 '--concurrently',
 241                 '--index' => $toast_index,
 242                 '--tablespace' => $tbspace_name,
 243                 'postgres',
 244         ],
 245         1,
 246         [],
 247         [qr/cannot move system relation/],
 248         'reindex toast index concurrently with tablespace');
 249
 250 # connection strings
 251 $node->command_ok([qw(reindexdb --echo --table=pg_am dbname=template1)],
 252         'reindexdb table with connection string');
 253 $node->command_ok(
 254         [qw(reindexdb --echo dbname=template1)],
 255         'reindexdb database with connection string');
 256 $node->command_ok(
 257         [qw(reindexdb --echo --system dbname=template1)],
 258         'reindexdb system with connection string');
 259
 260 # parallel processing
 261 $node->safe_psql(
 262         'postgres', q|
 263         CREATE SCHEMA s1;
 264         CREATE TABLE s1.t1(id integer);
 265         CREATE INDEX ON s1.t1(id);
 266         CREATE INDEX i1 ON s1.t1(id);
 267         CREATE SCHEMA s2;
 268         CREATE TABLE s2.t2(id integer);
 269         CREATE INDEX ON s2.t2(id);
 270         CREATE INDEX i2 ON s2.t2(id);
 271         -- empty schema
 272         CREATE SCHEMA s3;
 273 |);
 274
 275 $node->command_fails(
 276         [ 'reindexdb', '--jobs' => '2', '--system', 'postgres' ],
 277         'parallel reindexdb cannot process system catalogs');
 278 $node->command_ok(
 279         [
 280                 'reindexdb',
 281                 '--jobs' => '2',
 282                 '--index' => 's1.i1',
 283                 '--index' => 's2.i2',
 284                 'postgres',
 285         ],
 286         'parallel reindexdb for indices');
 287 # Note that the ordering of the commands is not stable, so the second
 288 # command for s2.t2 is not checked after.
 289 $node->issues_sql_like(
 290         [
 291                 'reindexdb',
 292                 '--jobs' => '2',
 293                 '--schema' => 's1',
 294                 '--schema' => 's2',
 295                 'postgres',
 296         ],
 297         qr/statement:\ REINDEX TABLE s1.t1;/,
 298         'parallel reindexdb for schemas does a per-table REINDEX');
 299 $node->command_ok([ 'reindexdb', '--jobs' => '2', '--schema' => 's3' ],
 300         'parallel reindexdb with empty schema');
 301 $node->command_ok(
 302         [
 303                 'reindexdb',
 304                 '--jobs' => '2',
 305                 '--concurrently',
 306                 '--dbname' => 'postgres',
 307         ],
 308         'parallel reindexdb on database, concurrently');
 309
 310 # combinations of objects
 311 $node->issues_sql_like(
 312         [ 'reindexdb', '--system', '--table' => 'test1', 'postgres' ],
 313         qr/statement:\ REINDEX SYSTEM postgres;/,
 314         'specify both --system and --table');
 315 $node->issues_sql_like(
 316         [ 'reindexdb', '--system', '--index' => 'test1x', 'postgres' ],
 317         qr/statement:\ REINDEX INDEX public.test1x;/,
 318         'specify both --system and --index');
 319 $node->issues_sql_like(
 320         [ 'reindexdb', '--system', '--schema' => 'pg_catalog', 'postgres' ],
 321         qr/statement:\ REINDEX SCHEMA pg_catalog;/,
 322         'specify both --system and --schema');
 323
 324 done_testing();