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.
--
Justin
>From 9f1b7f2fb0849a810dc1fa5c1c03d5ff4e2e7d55 Mon Sep 17 00:00:00 2001
From: Gilles Darold <gil...@migops.com>
Date: Sun, 6 Mar 2022 09:39:37 +0100
Subject: [PATCH 1/3] vacuumdb --schema only
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Le 04/03/2022 à 11:56, Justin Pryzby a écrit :
> On Fri, Mar 04, 2022 at 10:11:28AM +0100, Gilles Darold wrote:
>> The attached patch implements that. Option -n | --schema can be used
>> multiple time and can not be used together with options -a or -t.
> Yes, thanks.
>
> I suggest there should also be an --exclude-schema.
>
>> I do not propose to extend the VACUUM and ANALYZE commands because their
>> current syntax doesn't allow me to see an easy way to do that
> I think this would be easy with the parenthesized syntax.
> I'm not suggesting to do it there, though.
>
>> + /*
>> + * When filtereing on schema name, filter by table is not allowed.
>> + * The schema name can already be set in a fqdn table name.
> set *to*
>
Attached a new patch version that adds the -N | --exclude-schema option
to the vacuumdb command as suggested. Documentation updated too.
I will add this patch to the commitfest unless there is cons about
adding these options.
--
Gilles Darold
---
doc/src/sgml/ref/vacuumdb.sgml | 64 ++++++++++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 9 +++
src/bin/scripts/t/101_vacuumdb_all.pl | 3 +
src/bin/scripts/vacuumdb.c | 79 ++++++++++++++++++++++++++-
4 files changed, 154 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 956c0f01cbc..378328afb3d 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 96a818a3c16..4c4f47e32a4 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 1dcf4117671..b122c995b15 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 4f6917fd392..3dca22e1c88 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -46,10 +46,12 @@ typedef struct vacuumingOptions
bool process_toast;
} vacuumingOptions;
+static bool schema_exclusion = false;
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,8 @@ main(int argc, char *argv[])
SimpleStringList tables = {NULL, NULL};
int concurrentCons = 1;
int tbl_count = 0;
+ SimpleStringList schemas = {NULL, NULL};
+ SimpleStringList schemas_excluded = {NULL, NULL};
/* initialize options */
memset(&vacopts, 0, sizeof(vacopts));
@@ -140,7 +146,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 +208,13 @@ main(int argc, char *argv[])
&vacopts.parallel_workers))
exit(1);
break;
+ case 'n': /* include schema(s) */
+ simple_string_list_append(&schemas, optarg);
+ break;
+ case 'N': /* exclude schema(s) */
+ simple_string_list_append(&schemas_excluded, optarg);
+ schema_exclusion = true;
+ break;
case 2:
maintenance_db = pg_strdup(optarg);
break;
@@ -341,6 +354,26 @@ 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 || schemas_excluded.head != NULL))
+ {
+ pg_log_error("cannot vacuum all tables in schema(s) and specific table(s) at the same time");
+ exit(1);
+ }
+
+ /*
+ * Options -n | --schema and -N | --exclude-schema
+ * can not be used together
+ */
+ if (schemas.head != NULL && schemas_excluded.head != NULL)
+ {
+ pg_log_error("cannot vacuum all tables in schema(s) and and exclude specific schema(s) at the same time");
+ exit(1);
+ }
+
/* Avoid opening extra connections. */
if (tbl_count && (concurrentCons > tbl_count))
concurrentCons = tbl_count;
@@ -352,6 +385,19 @@ 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)
+ {
+ pg_log_error("cannot vacuum specific schema(s) in all databases");
+ exit(1);
+ }
+
+ if (schemas_excluded.head != NULL)
+ {
+ pg_log_error("cannot exclude from 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 +433,7 @@ main(int argc, char *argv[])
{
vacuum_one_database(&cparams, &vacopts,
stage,
+ (schema_exclusion) ? &schemas_excluded : &schemas,
&tables,
concurrentCons,
progname, echo, quiet);
@@ -395,6 +442,7 @@ main(int argc, char *argv[])
else
vacuum_one_database(&cparams, &vacopts,
ANALYZE_NO_STAGE,
+ (schema_exclusion) ? &schemas_excluded : &schemas,
&tables,
concurrentCons,
progname, echo, quiet);
@@ -420,6 +468,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)
@@ -436,6 +485,7 @@ vacuum_one_database(ConnParams *cparams,
int ntups;
bool failed = false;
bool tables_listed = false;
+ bool schemas_listed = false;
bool has_where = false;
const char *initcmd;
const char *stage_commands[] = {
@@ -618,6 +668,29 @@ vacuum_one_database(ConnParams *cparams,
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) "])\n");
has_where = true;
+
+ 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");
}
/*
@@ -814,6 +887,7 @@ vacuum_all_databases(ConnParams *cparams,
vacuum_one_database(cparams, vacopts,
stage,
NULL,
+ NULL,
concurrentCons,
progname, echo, quiet);
}
@@ -828,6 +902,7 @@ vacuum_all_databases(ConnParams *cparams,
vacuum_one_database(cparams, vacopts,
ANALYZE_NO_STAGE,
NULL,
+ NULL,
concurrentCons,
progname, echo, quiet);
}
@@ -1027,6 +1102,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"));
--
2.17.1
>From 14d518ae31e0110a4325f922882955ead554ca64 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 6 Mar 2022 08:18:20 -0600
Subject: [PATCH 2/3] f1
---
src/bin/scripts/vacuumdb.c | 48 ++++++++++++++++++--------------------
1 file changed, 23 insertions(+), 25 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 3dca22e1c88..f76d8472c1f 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -485,7 +485,6 @@ vacuum_one_database(ConnParams *cparams,
int ntups;
bool failed = false;
bool tables_listed = false;
- bool schemas_listed = false;
bool has_where = false;
const char *initcmd;
const char *stage_commands[] = {
@@ -655,42 +654,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;
- for (cell = schemas ? schemas->head : NULL; cell; cell = cell->next)
+ if (schemas != NULL && schemas->head != NULL)
{
- if (!schemas_listed) {
+ appendPQExpBufferStr(&catalog_query,
+ " AND c.relnamespace");
+ if (schema_exclusion)
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;
- }
+ " OPERATOR(pg_catalog.!=) ALL (ARRAY[");
else
- appendPQExpBufferStr(&catalog_query, ", ");
+ appendPQExpBufferStr(&catalog_query,
+ " OPERATOR(pg_catalog.=) ANY (ARRAY[");
- appendStringLiteralConn(&catalog_query, cell->val, conn);
- appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace::pg_catalog.name");
+ 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");
}
- /* Finish formatting schema filter */
- if (schemas_listed)
- appendPQExpBufferStr(&catalog_query, ")\n");
}
/*
--
2.17.1
>From 139a9cb7acd969848ee55fc4c08619cff4376c66 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 6 Mar 2022 09:02:13 -0600
Subject: [PATCH 3/3] f2
---
src/bin/scripts/vacuumdb.c | 51 +++++++++++++++++++-------------------
1 file changed, 25 insertions(+), 26 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index f76d8472c1f..9ed2b95d7a5 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -46,7 +46,7 @@ typedef struct vacuumingOptions
bool process_toast;
} vacuumingOptions;
-static bool schema_exclusion = false;
+enum trivalue schema_is_exclude = TRI_DEFAULT;
static void vacuum_one_database(ConnParams *cparams,
vacuumingOptions *vacopts,
@@ -130,7 +130,6 @@ main(int argc, char *argv[])
int concurrentCons = 1;
int tbl_count = 0;
SimpleStringList schemas = {NULL, NULL};
- SimpleStringList schemas_excluded = {NULL, NULL};
/* initialize options */
memset(&vacopts, 0, sizeof(vacopts));
@@ -209,11 +208,24 @@ main(int argc, char *argv[])
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) */
- simple_string_list_append(&schemas_excluded, optarg);
- schema_exclusion = true;
+ 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);
@@ -358,22 +370,12 @@ main(int argc, char *argv[])
* 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 || schemas_excluded.head != NULL))
+ 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);
}
- /*
- * Options -n | --schema and -N | --exclude-schema
- * can not be used together
- */
- if (schemas.head != NULL && schemas_excluded.head != NULL)
- {
- pg_log_error("cannot vacuum all tables in schema(s) and and exclude specific schema(s) at the same time");
- exit(1);
- }
-
/* Avoid opening extra connections. */
if (tbl_count && (concurrentCons > tbl_count))
concurrentCons = tbl_count;
@@ -388,13 +390,10 @@ main(int argc, char *argv[])
if (schemas.head != NULL)
{
- pg_log_error("cannot vacuum specific schema(s) in all databases");
- exit(1);
- }
-
- if (schemas_excluded.head != NULL)
- {
- pg_log_error("cannot exclude from vacuum specific schema(s) in all databases");
+ 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);
}
@@ -433,7 +432,7 @@ main(int argc, char *argv[])
{
vacuum_one_database(&cparams, &vacopts,
stage,
- (schema_exclusion) ? &schemas_excluded : &schemas,
+ &schemas,
&tables,
concurrentCons,
progname, echo, quiet);
@@ -442,7 +441,7 @@ main(int argc, char *argv[])
else
vacuum_one_database(&cparams, &vacopts,
ANALYZE_NO_STAGE,
- (schema_exclusion) ? &schemas_excluded : &schemas,
+ &schemas,
&tables,
concurrentCons,
progname, echo, quiet);
@@ -671,10 +670,10 @@ vacuum_one_database(ConnParams *cparams,
{
appendPQExpBufferStr(&catalog_query,
" AND c.relnamespace");
- if (schema_exclusion)
+ if (schema_is_exclude == TRI_YES)
appendPQExpBufferStr(&catalog_query,
" OPERATOR(pg_catalog.!=) ALL (ARRAY[");
- else
+ else if (schema_is_exclude == TRI_NO)
appendPQExpBufferStr(&catalog_query,
" OPERATOR(pg_catalog.=) ANY (ARRAY[");
--
2.17.1
