PostgreSQL Source Code: src/bin/scripts/reindexdb.c Source File (original) (raw)
1
2
3
4
5
6
7
8
9
10
11
13
14#include <limits.h>
15#include <stdlib.h>
16
17#include "catalog/pg_class_d.h"
26
28{
35
36
40 bool echo);
44 bool echo);
48 bool echo, bool verbose, bool concurrently,
49 int concurrentCons, const char *tablespace);
51 const char *progname, bool echo,
52 bool quiet, bool verbose, bool concurrently,
53 int concurrentCons, const char *tablespace,
58 const char *name, bool echo, bool verbose,
59 bool concurrently, const char *tablespace,
62 const char *name, bool echo,
64
66
67int
68main(int argc, char *argv[])
69{
70 static struct option long_options[] = {
89 {NULL, 0, NULL, 0}
90 };
91
93 int optindex;
94 int c;
95
96 const char *dbname = NULL;
97 const char *maintenance_db = NULL;
98 const char *host = NULL;
99 const char *port = NULL;
104 bool syscatalog = false;
105 bool alldb = false;
106 bool echo = false;
107 bool quiet = false;
109 bool concurrently = false;
113 int concurrentCons = 1;
114
118
120
121
122 while ((c = getopt_long(argc, argv, "ad:eh:i:j:qp:sS:t:U:vwW", long_options, &optindex)) != -1)
123 {
124 switch (c)
125 {
126 case 'a':
127 alldb = true;
128 break;
129 case 'd':
131 break;
132 case 'e':
133 echo = true;
134 break;
135 case 'h':
137 break;
138 case 'i':
140 break;
141 case 'j':
143 &concurrentCons))
144 exit(1);
145 break;
146 case 'q':
147 quiet = true;
148 break;
149 case 'p':
151 break;
152 case 's':
153 syscatalog = true;
154 break;
155 case 'S':
157 break;
158 case 't':
160 break;
161 case 'U':
163 break;
164 case 'v':
166 break;
167 case 'w':
168 prompt_password = TRI_NO;
169 break;
170 case 'W':
171 prompt_password = TRI_YES;
172 break;
173 case 1:
174 concurrently = true;
175 break;
176 case 2:
178 break;
179 case 3:
181 break;
182 default:
183
185 exit(1);
186 }
187 }
188
189
190
191
192
194 {
197 }
198
200 {
201 pg_log_error("too many command-line arguments (first is \"%s\")",
204 exit(1);
205 }
206
207
208 cparams.pghost = host;
213
215
216 if (concurrentCons > 1 && syscatalog)
217 pg_fatal("cannot use multiple jobs to reindex system catalogs");
218
219 if (alldb)
220 {
222 pg_fatal("cannot reindex all databases and a specific one at the same time");
223
224 cparams.dbname = maintenance_db;
225
227 concurrently, concurrentCons, tablespace,
228 syscatalog, &schemas, &tables, &indexes);
229 }
230 else
231 {
233 {
234 if (getenv("PGDATABASE"))
235 dbname = getenv("PGDATABASE");
236 else if (getenv("PGUSER"))
237 dbname = getenv("PGUSER");
238 else
240 }
241
243
244 if (syscatalog)
248
249 if (schemas.head != NULL)
252 concurrently, concurrentCons, tablespace);
253
254 if (indexes.head != NULL)
257 concurrently, concurrentCons, tablespace);
258
259 if (tables.head != NULL)
262 concurrently, concurrentCons, tablespace);
263
264
265
266
267
268 if (!syscatalog && indexes.head == NULL &&
269 tables.head == NULL && schemas.head == NULL)
272 concurrently, concurrentCons, tablespace);
273 }
274
275 exit(0);
276}
277
278static void
281 const char *progname, bool echo,
282 bool verbose, bool concurrently, int concurrentCons,
284{
288 bool parallel = concurrentCons > 1;
293 bool failed = false;
294 int items_count = 0;
296
298
300 {
302 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
303 "concurrently", "12");
304 }
305
307 {
309 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
310 "tablespace", "14");
311 }
312
313 if (!parallel)
314 {
315 switch (process_type)
316 {
319
320
321
322
323
324 Assert(user_list == NULL);
327 break;
328
332 process_list = user_list;
333 Assert(user_list != NULL);
334 break;
335 }
336 }
337 else
338 {
339 switch (process_type)
340 {
342 Assert(user_list != NULL);
343
344
346
347
349 user_list, echo);
351
352
353 if (process_list == NULL)
354 {
356 return;
357 }
358 break;
359
361 Assert(user_list != NULL);
362
363
364
365
366
368 echo);
369
370
371 if (tableoid_list == NULL)
372 {
374 return;
375 }
376
377 indices_tables_cell = tableoid_list->head;
378 process_list = user_list;
379 break;
380
382
383 process_list = NULL;
385 break;
386
388 process_list = user_list;
389 break;
390 }
391 }
392
393
394
395
396
397
398 items_count = 0;
399 for (cell = process_list->head; cell; cell = cell->next)
400 {
401 items_count++;
402
403
404 if (items_count >= concurrentCons)
405 break;
406 }
407 concurrentCons = Min(concurrentCons, items_count);
408 Assert(concurrentCons > 0);
409
410 Assert(process_list != NULL);
411
415
416 cell = process_list->head;
417 do
418 {
420 const char *objname = cell->val;
421
423 {
424 failed = true;
425 goto finish;
426 }
427
429 if (!free_slot)
430 {
431 failed = true;
432 goto finish;
433 }
434
438 {
439
440
441
442
443
444
447 while (indices_tables_cell->next &&
448 indices_tables_cell->val == indices_tables_cell->next->val)
449 {
450 indices_tables_cell = indices_tables_cell->next;
451 cell = cell->next;
452 objname = cell->val;
456 }
457 indices_tables_cell = indices_tables_cell->next;
458 }
459 else
460 {
463 }
465 echo, &sql);
467
468 cell = cell->next;
469 } while (cell != NULL);
470
472 failed = true;
473
474finish:
475 if (process_list != user_list)
476 {
479 }
480
481 if (tableoid_list)
482 {
485 }
486
489
490 if (failed)
491 exit(1);
492}
493
494
495
496
497
498static void
500 bool echo, bool verbose, bool concurrently,
502{
503 const char *paren = "(";
504 const char *comma = ", ";
505 const char *sep = paren;
506
508
509
511
513 {
516 }
517
519 {
523 }
524
525 if (sep != paren)
527
528
529 switch (type)
530 {
533 break;
536 break;
539 break;
542 break;
545 break;
546 }
547
548
549
550
551
552
553 if (concurrently)
555
556
557 switch (type)
558 {
563 break;
567 break;
570 break;
571 }
572
573
575}
576
577
578
579
580
581static void
584{
585 bool status;
586
587 if (echo)
589
591
592 if (!status)
593 {
594 switch (type)
595 {
597 pg_log_error("reindexing of database \"%s\" failed: %s",
599 break;
601 pg_log_error("reindexing of index \"%s\" in database \"%s\" failed: %s",
603 break;
605 pg_log_error("reindexing of schema \"%s\" in database \"%s\" failed: %s",
607 break;
609 pg_log_error("reindexing of system catalogs in database \"%s\" failed: %s",
611 break;
613 pg_log_error("reindexing of table \"%s\" in database \"%s\" failed: %s",
615 break;
616 }
617 }
618}
619
620
621
622
623
624
625
626
627
631{
635 int ntups;
636
638
639
640
641
642
643 switch (type)
644 {
646 Assert(user_list == NULL);
648 "SELECT c.relname, ns.nspname\n"
649 " FROM pg_catalog.pg_class c\n"
650 " JOIN pg_catalog.pg_namespace ns"
651 " ON c.relnamespace = ns.oid\n"
652 " WHERE ns.nspname != 'pg_catalog'\n"
653 " AND c.relkind IN ("
656 " AND c.relpersistence != "
658 " ORDER BY c.relpages DESC;");
659 break;
660
662 {
664
665 Assert(user_list != NULL);
666
667
668
669
670
672 "SELECT c.relname, ns.nspname\n"
673 " FROM pg_catalog.pg_class c\n"
674 " JOIN pg_catalog.pg_namespace ns"
675 " ON c.relnamespace = ns.oid\n"
676 " WHERE c.relkind IN ("
679 " AND c.relpersistence != "
681 " AND ns.nspname IN (");
682
683 for (cell = user_list->head; cell; cell = cell->next)
684 {
685 if (cell != user_list->head)
687
689 }
690
692 " ORDER BY c.relpages DESC;");
693 }
694 break;
695
700 break;
701 }
702
705
706
707
708
710 if (ntups == 0)
711 {
713 return NULL;
714 }
715
717
718
719 for (int i = 0; i < ntups; i++)
720 {
725 }
727
728 return tables;
729}
730
731
732
733
734
735
736
737
738
739
740
741
742
743static void
747 bool echo)
748{
752 int ntups;
753
754 Assert(index_list != NULL);
755
757
758
759
760
761
762
763
764
765
766
767
768
769
771 "SELECT x.indrelid, n.nspname, i.relname\n"
772 "FROM pg_catalog.pg_index x\n"
773 "JOIN pg_catalog.pg_class i ON i.oid = x.indexrelid\n"
774 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace\n"
775 "WHERE x.indexrelid = ANY(ARRAY['");
776
777 for (cell = index_list->head; cell; cell = cell->next)
778 {
779 if (cell != index_list->head)
781
783 }
784
785
786
787
788
790 "']::pg_catalog.regclass[])\n"
791 "ORDER BY max(i.relpages) OVER \n"
792 " (PARTITION BY x.indrelid),\n"
793 " x.indrelid, i.relpages;\n");
794
795
797 index_list->head = index_list->tail = NULL;
798
801
802
803
804
806 if (ntups == 0)
807 {
809 return;
810 }
811
813
814
815
816
817
818 for (int i = 0; i < ntups; i++)
819 {
825 }
826
828}
829
830static void
832 const char *progname, bool echo, bool quiet, bool verbose,
833 bool concurrently, int concurrentCons,
834 const char *tablespace, bool syscatalog,
837{
840 int i;
841
844 "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
845 echo);
847
849 {
851
852 if (!quiet)
853 {
856 }
857
859
860 if (syscatalog)
864
865 if (schemas->head != NULL)
868 concurrently, concurrentCons, tablespace);
869
870 if (indexes->head != NULL)
874
875 if (tables->head != NULL)
878 concurrently, concurrentCons, tablespace);
879
880
881
882
883
884 if (!syscatalog && indexes->head == NULL &&
885 tables->head == NULL && schemas->head == NULL)
888 concurrently, concurrentCons, tablespace);
889 }
890
892}
893
894static void
896{
897 printf(_("%s reindexes a PostgreSQL database.\n\n"), progname);
900 printf(_("\nOptions:\n"));
901 printf(_(" -a, --all reindex all databases\n"));
902 printf(_(" --concurrently reindex concurrently\n"));
903 printf(_(" -d, --dbname=DBNAME database to reindex\n"));
904 printf(_(" -e, --echo show the commands being sent to the server\n"));
905 printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
906 printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
907 printf(_(" -q, --quiet don't write any messages\n"));
908 printf(_(" -s, --system reindex system catalogs only\n"));
909 printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
910 printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
911 printf(_(" --tablespace=TABLESPACE tablespace where indexes are rebuilt\n"));
912 printf(_(" -v, --verbose write a lot of output\n"));
913 printf(_(" -V, --version output version information, then exit\n"));
914 printf(_(" -?, --help show this help, then exit\n"));
915 printf(_("\nConnection options:\n"));
916 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
917 printf(_(" -p, --port=PORT database server port\n"));
918 printf(_(" -U, --username=USERNAME user name to connect as\n"));
919 printf(_(" -w, --no-password never prompt for password\n"));
920 printf(_(" -W, --password force password prompt\n"));
921 printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
922 printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
923 printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
924 printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
925}
void appendQualifiedRelation(PQExpBuffer buf, const char *spec, PGconn *conn, bool echo)
#define PG_TEXTDOMAIN(domain)
volatile sig_atomic_t CancelRequested
void setup_cancel_handler(void(*query_cancel_callback)(void))
void set_pglocale_pgservice(const char *argv0, const char *app)
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
PGconn * connectDatabase(const ConnParams *cparams, const char *progname, bool echo, bool fail_ok, bool allow_password_reuse)
PGresult * executeQuery(PGconn *conn, const char *query)
int PQserverVersion(const PGconn *conn)
char * PQdb(const PGconn *conn)
int PQclientEncoding(const PGconn *conn)
void PQfinish(PGconn *conn)
char * PQerrorMessage(const PGconn *conn)
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
void PQclear(PGresult *res)
int PQntuples(const PGresult *res)
int PQsendQuery(PGconn *conn, const char *query)
char * pg_strdup(const char *in)
void * pg_malloc0(size_t size)
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
#define required_argument
Assert(PointerIsAligned(start, uint64))
void pg_logging_init(const char *argv0)
#define pg_log_error(...)
#define pg_log_error_hint(...)
void pfree(void *pointer)
bool option_parse_int(const char *optarg, const char *optname, int min_range, int max_range, int *result)
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
ParallelSlotArray * ParallelSlotsSetup(int numslots, ConnParams *cparams, const char *progname, bool echo, const char *initcmd)
bool ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
bool TableCommandResultHandler(PGresult *res, PGconn *conn, void *context)
ParallelSlot * ParallelSlotsGetIdle(ParallelSlotArray *sa, const char *dbname)
void ParallelSlotsTerminate(ParallelSlotArray *sa)
void ParallelSlotsAdoptConn(ParallelSlotArray *sa, PGconn *conn)
static void ParallelSlotSetHandler(ParallelSlot *slot, ParallelSlotResultHandler handler, void *context)
PGDLLIMPORT char * optarg
const char * get_progname(const char *argv0)
void initPQExpBuffer(PQExpBuffer str)
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
void appendPQExpBufferChar(PQExpBuffer str, char ch)
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
void termPQExpBuffer(PQExpBuffer str)
int main(int argc, char *argv[])
static void get_parallel_tabidx_list(PGconn *conn, SimpleStringList *index_list, SimpleOidList **table_list, bool echo)
static void reindex_all_databases(ConnParams *cparams, const char *progname, bool echo, bool quiet, bool verbose, bool concurrently, int concurrentCons, const char *tablespace, bool syscatalog, SimpleStringList *schemas, SimpleStringList *tables, SimpleStringList *indexes)
static void help(const char *progname)
static SimpleStringList * get_parallel_tables_list(PGconn *conn, ReindexType type, SimpleStringList *user_list, bool echo)
static void reindex_one_database(ConnParams *cparams, ReindexType type, SimpleStringList *user_list, const char *progname, bool echo, bool verbose, bool concurrently, int concurrentCons, const char *tablespace)
static void run_reindex_command(PGconn *conn, ReindexType type, const char *name, bool echo, PQExpBufferData *sql)
static void gen_reindex_command(PGconn *conn, ReindexType type, const char *name, bool echo, bool verbose, bool concurrently, const char *tablespace, PQExpBufferData *sql)
void simple_oid_list_destroy(SimpleOidList *list)
void simple_string_list_append(SimpleStringList *list, const char *val)
void simple_string_list_destroy(SimpleStringList *list)
void simple_oid_list_append(SimpleOidList *list, Oid val)
const char * fmtIdEnc(const char *rawid, int encoding)
const char * fmtQualifiedIdEnc(const char *schema, const char *id, int encoding)
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
struct SimpleOidListCell * next
char val[FLEXIBLE_ARRAY_MEMBER]
struct SimpleStringListCell * next
SimpleStringListCell * head
SimpleStringListCell * tail
enum trivalue prompt_password
const char * get_user_name_or_exit(const char *progname)