Hi, New version v4 of the patch to fix a typo in a comment.
-- Gilles Darold
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 956c0f01cb..378328afb3 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -39,6 +39,40 @@ PostgreSQL documentation <arg choice="opt"><replaceable>dbname</replaceable></arg> </cmdsynopsis> + <cmdsynopsis> + <command>vacuumdb</command> + <arg rep="repeat"><replaceable>connection-option</replaceable></arg> + <arg rep="repeat"><replaceable>option</replaceable></arg> + + <arg choice="plain" rep="repeat"> + <arg choice="opt"> + <group choice="plain"> + <arg choice="plain"> + <arg choice="opt"> + <group choice="plain"> + <arg choice="plain"><option>-n</option></arg> + <arg choice="plain"><option>--schema</option></arg> + </group> + <replaceable>schema</replaceable> + </arg> + </arg> + + <arg choice="plain"> + <arg choice="opt"> + <group choice="plain"> + <arg choice="plain"><option>-N</option></arg> + <arg choice="plain"><option>--exclude-schema</option></arg> + </group> + <replaceable>schema</replaceable> + </arg> + </arg> + </group> + </arg> + </arg> + + <arg choice="opt"><replaceable>dbname</replaceable></arg> + </cmdsynopsis> + <cmdsynopsis> <command>vacuumdb</command> <arg rep="repeat"><replaceable>connection-option</replaceable></arg> @@ -244,6 +278,28 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>-n <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Clean or analyze all tables in <replaceable class="parameter">schema</replaceable> only. + Multiple schemas can be vacuumed by writing multiple <option>-n</option> switches. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-N <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Clean or analyze all tables NOT in <replaceable class="parameter">schema</replaceable>. + Multiple schemas can be excluded from the vacuum by writing multiple <option>-N</option> switches. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>--no-index-cleanup</option></term> <listitem> @@ -619,6 +675,14 @@ PostgreSQL documentation <prompt>$ </prompt><userinput>vacuumdb --analyze --verbose --table='foo(bar)' xyzzy</userinput> </screen></para> + <para> + To clean all tables in the <literal>Foo</literal> and <literal>bar</literal> schemas + only in a database named <literal>xyzzy</literal>: +<screen> +<prompt>$ </prompt><userinput>vacuumdb --schema='"Foo"' --schema='bar' xyzzy</userinput> +</screen></para> + + </refsect1> <refsect1> diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index 96a818a3c1..4c4f47e32a 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -103,6 +103,8 @@ $node->safe_psql( CREATE TABLE funcidx (x int); INSERT INTO funcidx VALUES (0),(1),(2),(3); CREATE INDEX i0 ON funcidx ((f1(x))); + CREATE SCHEMA "Foo"; + CREATE TABLE "Foo".bar(id int); |); $node->command_ok([qw|vacuumdb -Z --table="need""q(uot"(")x") postgres|], 'column list'); @@ -146,5 +148,12 @@ $node->issues_sql_like( [ 'vacuumdb', '--min-xid-age', '2147483001', 'postgres' ], qr/GREATEST.*relfrozenxid.*2147483001/, 'vacuumdb --table --min-xid-age'); +$node->issues_sql_like( + [ 'vacuumdb', '--schema', '"Foo"', 'postgres' ], + qr/VACUUM "Foo".*/, + 'vacuumdb --schema schema only'); +$node->command_fails( + [ 'vacuumdb', '-n', 'pg_catalog', '-t pg_class', 'postgres' ], + 'cannot vacuum all tables in schema(s) and specific table(s) at the same time'); done_testing(); diff --git a/src/bin/scripts/t/101_vacuumdb_all.pl b/src/bin/scripts/t/101_vacuumdb_all.pl index 1dcf411767..b122c995b1 100644 --- a/src/bin/scripts/t/101_vacuumdb_all.pl +++ b/src/bin/scripts/t/101_vacuumdb_all.pl @@ -15,5 +15,8 @@ $node->issues_sql_like( [ 'vacuumdb', '-a' ], qr/statement: VACUUM.*statement: VACUUM/s, 'vacuum all databases'); +$node->command_fails( + [ 'vacuumdb', '-a', '-n', 'pg_catalog' ], + 'cannot vacuum specific schema(s) in all databases'); done_testing(); diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 4f6917fd39..d94f7459d5 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -46,10 +46,12 @@ typedef struct vacuumingOptions bool process_toast; } vacuumingOptions; +enum trivalue schema_is_exclude = TRI_DEFAULT; static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, + SimpleStringList *schemas, SimpleStringList *tables, int concurrentCons, const char *progname, bool echo, bool quiet); @@ -94,6 +96,8 @@ main(int argc, char *argv[]) {"verbose", no_argument, NULL, 'v'}, {"jobs", required_argument, NULL, 'j'}, {"parallel", required_argument, NULL, 'P'}, + {"schema", required_argument, NULL, 'n'}, + {"exclude-schema", required_argument, NULL, 'N'}, {"maintenance-db", required_argument, NULL, 2}, {"analyze-in-stages", no_argument, NULL, 3}, {"disable-page-skipping", no_argument, NULL, 4}, @@ -125,6 +129,7 @@ main(int argc, char *argv[]) SimpleStringList tables = {NULL, NULL}; int concurrentCons = 1; int tbl_count = 0; + SimpleStringList schemas = {NULL, NULL}; /* initialize options */ memset(&vacopts, 0, sizeof(vacopts)); @@ -140,7 +145,7 @@ main(int argc, char *argv[]) handle_help_version_opts(argc, argv, "vacuumdb", help); - while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:P:", long_options, &optindex)) != -1) + while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:P:n:N:", long_options, &optindex)) != -1) { switch (c) { @@ -202,6 +207,26 @@ main(int argc, char *argv[]) &vacopts.parallel_workers)) exit(1); break; + case 'n': /* include schema(s) */ + if (schema_is_exclude == TRI_YES) + { + pg_log_error("cannot vacuum all tables in schema(s) and exclude specific schema(s) at the same time"); + exit(1); + } + + simple_string_list_append(&schemas, optarg); + schema_is_exclude = TRI_NO; + break; + case 'N': /* exclude schema(s) */ + if (schema_is_exclude == TRI_NO) + { + pg_log_error("cannot vacuum all tables in schema(s) and exclude specific schema(s) at the same time"); + exit(1); + } + + simple_string_list_append(&schemas, optarg); + schema_is_exclude = TRI_YES; + break; case 2: maintenance_db = pg_strdup(optarg); break; @@ -341,6 +366,16 @@ main(int argc, char *argv[]) setup_cancel_handler(NULL); + /* + * When filtering on schema name, filter by table is not allowed. + * The schema name can already be set to a fqdn table name. + */ + if (tbl_count && (schemas.head != NULL)) + { + pg_log_error("cannot vacuum all tables in schema(s) and specific table(s) at the same time"); + exit(1); + } + /* Avoid opening extra connections. */ if (tbl_count && (concurrentCons > tbl_count)) concurrentCons = tbl_count; @@ -352,6 +387,16 @@ main(int argc, char *argv[]) pg_log_error("cannot vacuum all databases and a specific one at the same time"); exit(1); } + + if (schemas.head != NULL) + { + if (schema_is_exclude == TRI_YES) + pg_log_error("cannot exclude from vacuum specific schema(s) in all databases"); + else if (schema_is_exclude == TRI_NO) + pg_log_error("cannot vacuum specific schema(s) in all databases"); + exit(1); + } + if (tables.head != NULL) { pg_log_error("cannot vacuum specific table(s) in all databases"); @@ -387,6 +432,7 @@ main(int argc, char *argv[]) { vacuum_one_database(&cparams, &vacopts, stage, + &schemas, &tables, concurrentCons, progname, echo, quiet); @@ -395,6 +441,7 @@ main(int argc, char *argv[]) else vacuum_one_database(&cparams, &vacopts, ANALYZE_NO_STAGE, + &schemas, &tables, concurrentCons, progname, echo, quiet); @@ -420,6 +467,7 @@ static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, + SimpleStringList *schemas, SimpleStringList *tables, int concurrentCons, const char *progname, bool echo, bool quiet) @@ -605,19 +653,41 @@ vacuum_one_database(ConnParams *cparams, if (tables_listed) appendPQExpBufferStr(&catalog_query, " JOIN listed_tables" " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n"); - - /* - * If no tables were listed, filter for the relevant relation types. If - * tables were given via --table, don't bother filtering by relation type. - * Instead, let the server decide whether a given relation can be - * processed in which case the user will know about it. - */ - if (!tables_listed) + else { + /* + * If no tables were listed, filter for the relevant relation types. If + * tables were given via --table, don't bother filtering by relation type. + * Instead, let the server decide whether a given relation can be + * processed in which case the user will know about it. + */ appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array[" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) "])\n"); has_where = true; + + if (schemas != NULL && schemas->head != NULL) + { + appendPQExpBufferStr(&catalog_query, + " AND c.relnamespace"); + if (schema_is_exclude == TRI_YES) + appendPQExpBufferStr(&catalog_query, + " OPERATOR(pg_catalog.!=) ALL (ARRAY["); + else if (schema_is_exclude == TRI_NO) + appendPQExpBufferStr(&catalog_query, + " OPERATOR(pg_catalog.=) ANY (ARRAY["); + + for (cell = schemas->head; cell != NULL; cell = cell->next) + { + appendStringLiteralConn(&catalog_query, cell->val, conn); + + if (cell->next != NULL) + appendPQExpBufferStr(&catalog_query, ", "); + } + + /* Finish formatting schema filter */ + appendPQExpBufferStr(&catalog_query, "]::pg_catalog.regnamespace[])\n"); + } } /* @@ -814,6 +884,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, stage, NULL, + NULL, concurrentCons, progname, echo, quiet); } @@ -828,6 +899,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, ANALYZE_NO_STAGE, NULL, + NULL, concurrentCons, progname, echo, quiet); } @@ -1027,6 +1099,8 @@ help(const char *progname) printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n")); printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n")); printf(_(" --no-truncate don't truncate empty pages at the end of the table\n")); + printf(_(" -n, --schema=PATTERN vacuum tables in the specified schema(s) only\n")); + printf(_(" -N, --exclude-schema=PATTERN do NOT vacuum tables in the specified schema(s)\n")); printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));