Le 06/03/2022 à 16:04, Justin Pryzby a écrit :
> On Sun, Mar 06, 2022 at 09:39:37AM +0100, Gilles Darold wrote:
>> Attached a new patch version that adds the -N | --exclude-schema option
>> to the vacuumdb command as suggested. Documentation updated too.
>>
>> + pg_log_error("cannot vacuum all tables in schema(s) and and
>> exclude specific schema(s) at the same time");
> and and
>
> It's odd that schema_exclusion is a global var, but schemas/excluded are not.
>
> Also, it seems unnecessary to have two schemas vars, since they can't be used
> together. Maybe there's a better way than what I did in 003.
>
>> + for (cell = schemas ? schemas->head : NULL; cell; cell =
>> cell->next)
> It's preferred to write cell != NULL
>
>> + bool schemas_listed = false;
> ...
>> + for (cell = schemas ? schemas->head : NULL; cell; cell =
>> cell->next)
>> + {
>> + if (!schemas_listed) {
>> + appendPQExpBufferStr(&catalog_query,
>> + " AND
>> pg_catalog.quote_ident(ns.nspname)");
>> + if (schema_exclusion)
>> + appendPQExpBufferStr(&catalog_query, "
>> NOT IN (");
>> + else
>> + appendPQExpBufferStr(&catalog_query, "
>> IN (");
>> +
>> + schemas_listed = true;
>> + }
>> + else
>> + appendPQExpBufferStr(&catalog_query, ", ");
>> +
>> + appendStringLiteralConn(&catalog_query, cell->val,
>> conn);
>> + appendPQExpBufferStr(&catalog_query,
>> "::pg_catalog.regnamespace::pg_catalog.name");
>> +
>> + }
>> + /* Finish formatting schema filter */
>> + if (schemas_listed)
>> + appendPQExpBufferStr(&catalog_query, ")\n");
>> }
> Maybe it's clearer to write this with =ANY() / != ALL() ?
> See 002.
>
I have applied your changes and produced a new version v3 of the patch,
thanks for the improvements. The patch have been added to commitfest
interface, see here https://commitfest.postgresql.org/38/3587/
--
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..9ed2b95d7a 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 filtereing 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"));
