Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit57e6db7

Browse files
committed
Add --tablespace option to reindexdb
This option provides REINDEX (TABLESPACE) for reindexdb, applying thetablespace value given by the caller to all the REINDEX queriesgenerated.While on it, this commit adds some tests for REINDEX TABLESPACE, withand without CONCURRENTLY, when run on toast indexes and tables. Suchoperations are not allowed, and toast relation names are not stableenough to be part of the main regression test suite (even if using a PLfunction with a TRY/CATCH logic, as CONCURRENTLY could not be tested).Author: Michael PaquierReviewed-by: Mark Dilger, Daniel GustafssonDiscussion:https://postgr.es/m/YDiaDMnzLICqeukl@paquier.xyz
1 parent5b2f2af commit57e6db7

File tree

3 files changed

+163
-37
lines changed

3 files changed

+163
-37
lines changed

‎doc/src/sgml/ref/reindexdb.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -237,6 +237,16 @@ PostgreSQL documentation
237237
</listitem>
238238
</varlistentry>
239239

240+
<varlistentry>
241+
<term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></option></term>
242+
<listitem>
243+
<para>
244+
Specifies the tablespace where indexes are rebuilt. (This name is
245+
processed as a double-quoted identifier.)
246+
</para>
247+
</listitem>
248+
</varlistentry>
249+
240250
<varlistentry>
241251
<term><option>-v</option></term>
242252
<term><option>--verbose</option></term>

‎src/bin/scripts/reindexdb.c

Lines changed: 74 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -40,14 +40,15 @@ static void reindex_one_database(const ConnParams *cparams, ReindexType type,
4040
SimpleStringList*user_list,
4141
constchar*progname,
4242
boolecho,boolverbose,boolconcurrently,
43-
intconcurrentCons);
43+
intconcurrentCons,constchar*tablespace);
4444
staticvoidreindex_all_databases(ConnParams*cparams,
4545
constchar*progname,boolecho,
4646
boolquiet,boolverbose,boolconcurrently,
47-
intconcurrentCons);
47+
intconcurrentCons,constchar*tablespace);
4848
staticvoidrun_reindex_command(PGconn*conn,ReindexTypetype,
4949
constchar*name,boolecho,boolverbose,
50-
boolconcurrently,boolasync);
50+
boolconcurrently,boolasync,
51+
constchar*tablespace);
5152

5253
staticvoidhelp(constchar*progname);
5354

@@ -72,6 +73,7 @@ main(int argc, char *argv[])
7273
{"verbose",no_argument,NULL,'v'},
7374
{"concurrently",no_argument,NULL,1},
7475
{"maintenance-db",required_argument,NULL,2},
76+
{"tablespace",required_argument,NULL,3},
7577
{NULL,0,NULL,0}
7678
};
7779

@@ -84,6 +86,7 @@ main(int argc, char *argv[])
8486
constchar*host=NULL;
8587
constchar*port=NULL;
8688
constchar*username=NULL;
89+
constchar*tablespace=NULL;
8790
enumtrivalueprompt_password=TRI_DEFAULT;
8891
ConnParamscparams;
8992
boolsyscatalog= false;
@@ -164,6 +167,9 @@ main(int argc, char *argv[])
164167
case2:
165168
maintenance_db=pg_strdup(optarg);
166169
break;
170+
case3:
171+
tablespace=pg_strdup(optarg);
172+
break;
167173
default:
168174
fprintf(stderr,_("Try \"%s --help\" for more information.\n"),progname);
169175
exit(1);
@@ -228,7 +234,7 @@ main(int argc, char *argv[])
228234
cparams.dbname=maintenance_db;
229235

230236
reindex_all_databases(&cparams,progname,echo,quiet,verbose,
231-
concurrently,concurrentCons);
237+
concurrently,concurrentCons,tablespace);
232238
}
233239
elseif (syscatalog)
234240
{
@@ -268,7 +274,7 @@ main(int argc, char *argv[])
268274

269275
reindex_one_database(&cparams,REINDEX_SYSTEM,NULL,
270276
progname,echo,verbose,
271-
concurrently,1);
277+
concurrently,1,tablespace);
272278
}
273279
else
274280
{
@@ -298,17 +304,17 @@ main(int argc, char *argv[])
298304
if (schemas.head!=NULL)
299305
reindex_one_database(&cparams,REINDEX_SCHEMA,&schemas,
300306
progname,echo,verbose,
301-
concurrently,concurrentCons);
307+
concurrently,concurrentCons,tablespace);
302308

303309
if (indexes.head!=NULL)
304310
reindex_one_database(&cparams,REINDEX_INDEX,&indexes,
305311
progname,echo,verbose,
306-
concurrently,1);
312+
concurrently,1,tablespace);
307313

308314
if (tables.head!=NULL)
309315
reindex_one_database(&cparams,REINDEX_TABLE,&tables,
310316
progname,echo,verbose,
311-
concurrently,concurrentCons);
317+
concurrently,concurrentCons,tablespace);
312318

313319
/*
314320
* reindex database only if neither index nor table nor schema is
@@ -317,7 +323,7 @@ main(int argc, char *argv[])
317323
if (indexes.head==NULL&&tables.head==NULL&&schemas.head==NULL)
318324
reindex_one_database(&cparams,REINDEX_DATABASE,NULL,
319325
progname,echo,verbose,
320-
concurrently,concurrentCons);
326+
concurrently,concurrentCons,tablespace);
321327
}
322328

323329
exit(0);
@@ -327,7 +333,8 @@ static void
327333
reindex_one_database(constConnParams*cparams,ReindexTypetype,
328334
SimpleStringList*user_list,
329335
constchar*progname,boolecho,
330-
boolverbose,boolconcurrently,intconcurrentCons)
336+
boolverbose,boolconcurrently,intconcurrentCons,
337+
constchar*tablespace)
331338
{
332339
PGconn*conn;
333340
SimpleStringListCell*cell;
@@ -348,6 +355,14 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
348355
exit(1);
349356
}
350357

358+
if (tablespace&&PQserverVersion(conn)<140000)
359+
{
360+
PQfinish(conn);
361+
pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
362+
"tablespace","14");
363+
exit(1);
364+
}
365+
351366
if (!parallel)
352367
{
353368
switch (process_type)
@@ -386,7 +401,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
386401
pg_log_warning("cannot reindex system catalogs concurrently, skipping all");
387402
else
388403
run_reindex_command(conn,REINDEX_SYSTEM,PQdb(conn),echo,
389-
verbose,concurrently, false);
404+
verbose,concurrently, false,
405+
tablespace);
390406

391407
/* Build a list of relations from the database */
392408
process_list=get_parallel_object_list(conn,process_type,
@@ -468,7 +484,7 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
468484

469485
ParallelSlotSetHandler(free_slot,TableCommandResultHandler,NULL);
470486
run_reindex_command(free_slot->connection,process_type,objname,
471-
echo,verbose,concurrently, true);
487+
echo,verbose,concurrently, true,tablespace);
472488

473489
cell=cell->next;
474490
}while (cell!=NULL);
@@ -492,8 +508,12 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
492508

493509
staticvoid
494510
run_reindex_command(PGconn*conn,ReindexTypetype,constchar*name,
495-
boolecho,boolverbose,boolconcurrently,boolasync)
511+
boolecho,boolverbose,boolconcurrently,boolasync,
512+
constchar*tablespace)
496513
{
514+
constchar*paren="(";
515+
constchar*comma=", ";
516+
constchar*sep=paren;
497517
PQExpBufferDatasql;
498518
boolstatus;
499519

@@ -505,7 +525,19 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
505525
appendPQExpBufferStr(&sql,"REINDEX ");
506526

507527
if (verbose)
508-
appendPQExpBufferStr(&sql,"(VERBOSE) ");
528+
{
529+
appendPQExpBuffer(&sql,"%sVERBOSE",sep);
530+
sep=comma;
531+
}
532+
533+
if (tablespace)
534+
{
535+
appendPQExpBuffer(&sql,"%sTABLESPACE %s",sep,fmtId(tablespace));
536+
sep=comma;
537+
}
538+
539+
if (sep!=paren)
540+
appendPQExpBufferStr(&sql,") ");
509541

510542
/* object type */
511543
switch (type)
@@ -527,6 +559,11 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
527559
break;
528560
}
529561

562+
/*
563+
* Parenthesized grammar is only supported for CONCURRENTLY since
564+
* PostgreSQL 14. Since 12, CONCURRENTLY can be specified after the
565+
* object type.
566+
*/
530567
if (concurrently)
531568
appendPQExpBufferStr(&sql,"CONCURRENTLY ");
532569

@@ -716,7 +753,8 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
716753
staticvoid
717754
reindex_all_databases(ConnParams*cparams,
718755
constchar*progname,boolecho,boolquiet,boolverbose,
719-
boolconcurrently,intconcurrentCons)
756+
boolconcurrently,intconcurrentCons,
757+
constchar*tablespace)
720758
{
721759
PGconn*conn;
722760
PGresult*result;
@@ -740,7 +778,7 @@ reindex_all_databases(ConnParams *cparams,
740778

741779
reindex_one_database(cparams,REINDEX_DATABASE,NULL,
742780
progname,echo,verbose,concurrently,
743-
concurrentCons);
781+
concurrentCons,tablespace);
744782
}
745783

746784
PQclear(result);
@@ -753,26 +791,27 @@ help(const char *progname)
753791
printf(_("Usage:\n"));
754792
printf(_(" %s [OPTION]... [DBNAME]\n"),progname);
755793
printf(_("\nOptions:\n"));
756-
printf(_(" -a, --all reindex all databases\n"));
757-
printf(_(" --concurrently reindex concurrently\n"));
758-
printf(_(" -d, --dbname=DBNAME database to reindex\n"));
759-
printf(_(" -e, --echo show the commands being sent to the server\n"));
760-
printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
761-
printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
762-
printf(_(" -q, --quiet don't write any messages\n"));
763-
printf(_(" -s, --system reindex system catalogs\n"));
764-
printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
765-
printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
766-
printf(_(" -v, --verbose write a lot of output\n"));
767-
printf(_(" -V, --version output version information, then exit\n"));
768-
printf(_(" -?, --help show this help, then exit\n"));
794+
printf(_(" -a, --all reindex all databases\n"));
795+
printf(_(" --concurrently reindex concurrently\n"));
796+
printf(_(" -d, --dbname=DBNAME database to reindex\n"));
797+
printf(_(" -e, --echo show the commands being sent to the server\n"));
798+
printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
799+
printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
800+
printf(_(" -q, --quiet don't write any messages\n"));
801+
printf(_(" -s, --system reindex system catalogs\n"));
802+
printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
803+
printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
804+
printf(_(" --tablespace=TABLESPACE tablespace where indexes are rebuilt\n"));
805+
printf(_(" -v, --verbose write a lot of output\n"));
806+
printf(_(" -V, --version output version information, then exit\n"));
807+
printf(_(" -?, --help show this help, then exit\n"));
769808
printf(_("\nConnection options:\n"));
770-
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
771-
printf(_(" -p, --port=PORT database server port\n"));
772-
printf(_(" -U, --username=USERNAME user name to connect as\n"));
773-
printf(_(" -w, --no-password never prompt for password\n"));
774-
printf(_(" -W, --password force password prompt\n"));
775-
printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
809+
printf(_(" -h, --host=HOSTNAMEdatabase server host or socket directory\n"));
810+
printf(_(" -p, --port=PORTdatabase server port\n"));
811+
printf(_(" -U, --username=USERNAMEuser name to connect as\n"));
812+
printf(_(" -w, --no-passwordnever prompt for password\n"));
813+
printf(_(" -W, --passwordforce password prompt\n"));
814+
printf(_(" --maintenance-db=DBNAMEalternate maintenance database\n"));
776815
printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
777816
printf(_("\nReport bugs to <%s>.\n"),PACKAGE_BUGREPORT);
778817
printf(_("%s home page: <%s>\n"),PACKAGE_NAME,PACKAGE_URL);

‎src/bin/scripts/t/090_reindexdb.pl

Lines changed: 79 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

44
use PostgresNode;
55
use TestLib;
6-
use Test::Moretests=>44;
6+
use Test::Moretests=>58;
77

88
program_help_ok('reindexdb');
99
program_version_ok('reindexdb');
@@ -15,17 +15,38 @@
1515

1616
$ENV{PGOPTIONS} ='--client-min-messages=WARNING';
1717

18+
# Create a tablespace for testing.
19+
my$tbspace_path =$node->basedir .'/regress_reindex_tbspace';
20+
mkdir$tbspace_pathordie"cannot create directory$tbspace_path";
21+
$tbspace_path = TestLib::perl2host($tbspace_path);
22+
my$tbspace_name ='reindex_tbspace';
23+
$node->safe_psql('postgres',
24+
"CREATE TABLESPACE$tbspace_name LOCATION '$tbspace_path';");
25+
1826
$node->issues_sql_like(
1927
['reindexdb','postgres' ],
2028
qr/statement: REINDEX DATABASE postgres;/,
2129
'SQL REINDEX run');
2230

31+
# Use text as data type to get a toast table.
2332
$node->safe_psql('postgres',
24-
'CREATE TABLE test1 (a int); CREATE INDEX test1x ON test1 (a);');
33+
'CREATE TABLE test1 (a text); CREATE INDEX test1x ON test1 (a);');
34+
# Collect toast table and index names of this relation, for later use.
35+
my$toast_table =$node->safe_psql('postgres',
36+
"SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'test1'::regclass;"
37+
);
38+
my$toast_index =$node->safe_psql('postgres',
39+
"SELECT indexrelid::regclass FROM pg_index WHERE indrelid = '$toast_table'::regclass;"
40+
);
41+
2542
$node->issues_sql_like(
2643
['reindexdb','-t','test1','postgres' ],
2744
qr/statement: REINDEX TABLE public\.test1;/,
2845
'reindex specific table');
46+
$node->issues_sql_like(
47+
['reindexdb','-t','test1','--tablespace',$tbspace_name,'postgres' ],
48+
qr/statement: REINDEX\(TABLESPACE$tbspace_name\) TABLE public\.test1;/,
49+
'reindex specific table on tablespace');
2950
$node->issues_sql_like(
3051
['reindexdb','-i','test1x','postgres' ],
3152
qr/statement: REINDEX INDEX public\.test1x;/,
@@ -42,6 +63,13 @@
4263
['reindexdb','-v','-t','test1','postgres' ],
4364
qr/statement: REINDEX\(VERBOSE\) TABLE public\.test1;/,
4465
'reindex with verbose output');
66+
$node->issues_sql_like(
67+
[
68+
'reindexdb','-v','-t','test1',
69+
'--tablespace',$tbspace_name,'postgres'
70+
],
71+
qr/statement: REINDEX\(VERBOSE, TABLESPACE$tbspace_name\) TABLE public\.test1;/,
72+
'reindex with verbose output and tablespace');
4573

4674
# the same with --concurrently
4775
$node->issues_sql_like(
@@ -67,6 +95,55 @@
6795
['reindexdb','--concurrently','-v','-t','test1','postgres' ],
6896
qr/statement: REINDEX\(VERBOSE\) TABLE CONCURRENTLY public\.test1;/,
6997
'reindex with verbose output concurrently');
98+
$node->issues_sql_like(
99+
[
100+
'reindexdb','--concurrently','-v','-t',
101+
'test1','--tablespace',$tbspace_name,'postgres'
102+
],
103+
qr/statement: REINDEX\(VERBOSE, TABLESPACE$tbspace_name\) TABLE CONCURRENTLY public\.test1;/,
104+
'reindex concurrently with verbose output and tablespace');
105+
106+
# REINDEX TABLESPACE on toast indexes and tables fails. This is not
107+
# part of the main regression test suite as these have unpredictable
108+
# names, and CONCURRENTLY cannot be used in transaction blocks, preventing
109+
# the use of TRY/CATCH blocks in a custom function to filter error
110+
# messages.
111+
$node->command_checks_all(
112+
[
113+
'reindexdb','-t',$toast_table,'--tablespace',
114+
$tbspace_name,'postgres'
115+
],
116+
1,
117+
[],
118+
[qr/cannot move system relation/],
119+
'reindex toast table with tablespace');
120+
$node->command_checks_all(
121+
[
122+
'reindexdb','--concurrently','-t',$toast_table,
123+
'--tablespace',$tbspace_name,'postgres'
124+
],
125+
1,
126+
[],
127+
[qr/cannot move system relation/],
128+
'reindex toast table concurrently with tablespace');
129+
$node->command_checks_all(
130+
[
131+
'reindexdb','-i',$toast_index,'--tablespace',
132+
$tbspace_name,'postgres'
133+
],
134+
1,
135+
[],
136+
[qr/cannot move system relation/],
137+
'reindex toast index with tablespace');
138+
$node->command_checks_all(
139+
[
140+
'reindexdb','--concurrently','-i',$toast_index,
141+
'--tablespace',$tbspace_name,'postgres'
142+
],
143+
1,
144+
[],
145+
[qr/cannot move system relation/],
146+
'reindex toast index concurrently with tablespace');
70147

71148
# connection strings
72149
$node->command_ok([qw(reindexdb --echo --table=pg_am dbname=template1)],

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp