diff options
| author | Daniel Gustafsson <dgustafsson@postgresql.org> | 2023-09-25 16:03:17 +0200 |
|---|---|---|
| committer | Daniel Gustafsson <dgustafsson@postgresql.org> | 2023-09-25 16:03:17 +0200 |
| commit | fb56a181175bba03a6d4f82cd3c07ec5511ce545 (patch) | |
| tree | 1a19cea86866b90e5230a74d86b225201b0be88a | |
| parent | 2e3dc8c14811a8d5dc0cdeda408f63131b873da0 (diff) | |
| download | postgresql-fb56a181175bba03a6d4f82cd3c07ec5511ce545.tar.gz postgresql-fb56a181175bba03a6d4f82cd3c07ec5511ce545.zip | |
vacuumdb: Fix excluding multiple schemas with -N
When specifying multiple schemas to exclude with -N parameters, none
of the schemas are actually excluded (a single -N worked as expected).
This fixes the catalog query to handle multiple exclusions and adds a
test for this case.
Backpatch to v16 where this was introduced.
Author: Nathan Bossart <nathandbossart@gmail.com>
Author: Kuwamura Masaki <kuwamura@db.is.i.nagoya-u.ac.jp>
Reported-by: Kuwamura Masaki <kuwamura@db.is.i.nagoya-u.ac.jp>
Discussion: https://postgr.es/m/CAMyC8qp9mXPQd5D6s6CJxvmignsbTqGZwDDB6VYJOn1A8WG38w@mail.gmail.com
Backpatch-through: 16
| -rw-r--r-- | src/bin/scripts/t/100_vacuumdb.pl | 15 | ||||
| -rw-r--r-- | src/bin/scripts/vacuumdb.c | 26 |
2 files changed, 30 insertions, 11 deletions
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index eccfcc54a1a..925079bbedb 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -112,6 +112,8 @@ $node->safe_psql( CREATE INDEX i0 ON funcidx ((f1(x))); CREATE SCHEMA "Foo"; CREATE TABLE "Foo".bar(id int); + CREATE SCHEMA "Bar"; + CREATE TABLE "Bar".baz(id int); |); $node->command_ok([qw|vacuumdb -Z --table="need""q(uot"(")x") postgres|], 'column list'); @@ -160,9 +162,20 @@ $node->issues_sql_like( qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/, 'vacuumdb --schema'); $node->issues_sql_like( + [ 'vacuumdb', '--schema', '"Foo"', '--schema', '"Bar"', 'postgres' ], + qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar + .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz + /sx, + 'vacuumdb multiple --schema switches'); +$node->issues_sql_like( [ 'vacuumdb', '--exclude-schema', '"Foo"', 'postgres' ], - qr/(?:(?!VACUUM "Foo".bar).)*/, + qr/^(?!.*VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar).*$/s, 'vacuumdb --exclude-schema'); +$node->issues_sql_like( + [ 'vacuumdb', '--exclude-schema', '"Foo"', '--exclude-schema', '"Bar"', 'postgres' ], + qr/^(?!.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar + | VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz).*$/sx, + 'vacuumdb multiple --exclude-schema switches'); $node->command_fails_like( [ 'vacuumdb', '-N', 'pg_catalog', '-t', 'pg_class', 'postgres', ], qr/cannot vacuum specific table\(s\) and exclude schema\(s\) at the same time/, diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 005b064c06c..557ab5d8e13 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -678,18 +678,22 @@ vacuum_one_database(ConnParams *cparams, /* Used to match the tables or schemas listed by the user */ if (objects_listed) { - appendPQExpBufferStr(&catalog_query, " JOIN listed_objects" - " ON listed_objects.object_oid "); - - if (objfilter & OBJFILTER_SCHEMA_EXCLUDE) - appendPQExpBufferStr(&catalog_query, "OPERATOR(pg_catalog.!=) "); - else - appendPQExpBufferStr(&catalog_query, "OPERATOR(pg_catalog.=) "); + appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects" + " ON listed_objects.object_oid" + " OPERATOR(pg_catalog.=) "); if (objfilter & OBJFILTER_TABLE) appendPQExpBufferStr(&catalog_query, "c.oid\n"); else appendPQExpBufferStr(&catalog_query, "ns.oid\n"); + + if (objfilter & OBJFILTER_SCHEMA_EXCLUDE) + appendPQExpBuffer(&catalog_query, + " WHERE listed_objects.object_oid IS NULL\n"); + else + appendPQExpBuffer(&catalog_query, + " WHERE listed_objects.object_oid IS NOT NULL\n"); + has_where = true; } /* @@ -700,9 +704,11 @@ vacuum_one_database(ConnParams *cparams, */ if ((objfilter & OBJFILTER_TABLE) == 0) { - appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array[" - CppAsString2(RELKIND_RELATION) ", " - CppAsString2(RELKIND_MATVIEW) "])\n"); + appendPQExpBuffer(&catalog_query, + " %s c.relkind OPERATOR(pg_catalog.=) ANY (array[" + CppAsString2(RELKIND_RELATION) ", " + CppAsString2(RELKIND_MATVIEW) "])\n", + has_where ? "AND" : "WHERE"); has_where = true; } |