Debian Codegraph @

src/bin/scripts/t/090_reindexdb.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('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 '--index' => 's1.t1_id_idx',
285 '--index' => 's2.t2_id_idx',
286 'postgres',
287 ],
288 'parallel reindexdb for indices');
289 # Note that the ordering of the commands is not stable, so the second
290 # command for s2.t2 is not checked after.
291 $node->issues_sql_like(
292 [
293 'reindexdb',
294 '--jobs' => '2',
295 '--schema' => 's1',
296 '--schema' => 's2',
297 'postgres',
298 ],
299 qr/statement:\ REINDEX TABLE s1.t1;/,
300 'parallel reindexdb for schemas does a per-table REINDEX');
301 $node->command_ok([ 'reindexdb', '--jobs' => '2', '--schema' => 's3' ],
302 'parallel reindexdb with empty schema');
303 $node->command_ok(
304 [
305 'reindexdb',
306 '--jobs' => '2',
307 '--concurrently',
308 '--dbname' => 'postgres',
309 ],
310 'parallel reindexdb on database, concurrently');
311
312 # combinations of objects
313 $node->issues_sql_like(
314 [ 'reindexdb', '--system', '--table' => 'test1', 'postgres' ],
315 qr/statement:\ REINDEX SYSTEM postgres;/,
316 'specify both --system and --table');
317 $node->issues_sql_like(
318 [ 'reindexdb', '--system', '--index' => 'test1x', 'postgres' ],
319 qr/statement:\ REINDEX INDEX public.test1x;/,
320 'specify both --system and --index');
321 $node->issues_sql_like(
322 [ 'reindexdb', '--system', '--schema' => 'pg_catalog', 'postgres' ],
323 qr/statement:\ REINDEX SCHEMA pg_catalog;/,
324 'specify both --system and --schema');
325
326 done_testing();