diff options
| author | Michael Paquier <michael@paquier.xyz> | 2024-09-25 14:44:57 +0900 |
|---|---|---|
| committer | Michael Paquier <michael@paquier.xyz> | 2024-09-25 14:44:57 +0900 |
| commit | 60c618216ddbfcd65fc6c8078c1dbb775643662b (patch) | |
| tree | 68a51be27e65f9d84f87d6cf8ba5e48ac2e1b763 | |
| parent | f51b34b3eddbc501063f7b8ac470d26ce4e18a48 (diff) | |
| download | postgresql-60c618216ddbfcd65fc6c8078c1dbb775643662b.tar.gz postgresql-60c618216ddbfcd65fc6c8078c1dbb775643662b.zip | |
vacuumdb: Skip temporary tables in query to build list of relations
Running vacuumdb with a non-superuser while another user has created a
temporary table would lead to a mid-flight permission failure,
interrupting the operation. vacuum_rel() skips temporary relations of
other backends, and it makes no sense for vacuumdb to know about these
relations, so let's switch it to ignore temporary relations entirely.
Adding a qual in the query based on relpersistence simplifies the
generation of its WHERE clause in vacuum_one_database(), per se the
removal of "has_where".
Author: VaibhaveS, Michael Paquier
Reviewed-by: Fujii Masao
Discussion: https://postgr.es/m/CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com
Backpatch-through: 12
| -rw-r--r-- | src/bin/scripts/vacuumdb.c | 26 |
1 files changed, 16 insertions, 10 deletions
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index c884ffc8878..1792c35e592 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -447,7 +447,6 @@ vacuum_one_database(ConnParams *cparams, int ntups; bool failed = false; bool tables_listed = false; - bool has_where = false; const char *initcmd; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", @@ -612,12 +611,22 @@ vacuum_one_database(ConnParams *cparams, " LEFT JOIN pg_catalog.pg_class t" " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n"); - /* Used to match the tables listed by the user */ + /* + * Used to match the tables listed by the user, completing the JOIN + * clause. + */ if (tables_listed) appendPQExpBufferStr(&catalog_query, " JOIN listed_tables" " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n"); /* + * Exclude temporary tables, beginning the WHERE clause. + */ + appendPQExpBufferStr(&catalog_query, + " WHERE c.relpersistence != " CppAsString2(RELPERSISTENCE_TEMP)); + + + /* * 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 @@ -625,10 +634,9 @@ vacuum_one_database(ConnParams *cparams, */ if (!tables_listed) { - appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array[" + appendPQExpBufferStr(&catalog_query, " AND c.relkind OPERATOR(pg_catalog.=) ANY (array[" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) "])\n"); - has_where = true; } /* @@ -641,25 +649,23 @@ vacuum_one_database(ConnParams *cparams, if (vacopts->min_xid_age != 0) { appendPQExpBuffer(&catalog_query, - " %s GREATEST(pg_catalog.age(c.relfrozenxid)," + " AND GREATEST(pg_catalog.age(c.relfrozenxid)," " pg_catalog.age(t.relfrozenxid)) " " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n" " AND c.relfrozenxid OPERATOR(pg_catalog.!=)" " '0'::pg_catalog.xid\n", - has_where ? "AND" : "WHERE", vacopts->min_xid_age); - has_where = true; + vacopts->min_xid_age); } if (vacopts->min_mxid_age != 0) { appendPQExpBuffer(&catalog_query, - " %s GREATEST(pg_catalog.mxid_age(c.relminmxid)," + " AND GREATEST(pg_catalog.mxid_age(c.relminmxid)," " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)" " '%d'::pg_catalog.int4\n" " AND c.relminmxid OPERATOR(pg_catalog.!=)" " '0'::pg_catalog.xid\n", - has_where ? "AND" : "WHERE", vacopts->min_mxid_age); - has_where = true; + vacopts->min_mxid_age); } /* |