postgres.git / summary / log / commit / refs

commit    74eaa0544abf84d82bceb80e6f66321ec4c92c82
Author:   Michael Paquier <michael@paquier.xyz>
Date:     Wed Sep 25 05:44:56 2024 +0000
First released in: 18.0, 12.21, 13.17, 14.14, 15.9, 16.5, 17.1

    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


 src/bin/scripts/vacuumdb.c | 26 ++++++++++++++++----------
 1 file changed, 16 insertions(+), 10 deletions(-)

diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 2c576345058..3995e08c58d 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -401,7 +401,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;",
@@ -551,11 +550,21 @@ 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.
@@ -564,10 +573,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;
 	}
 
 	/*
@@ -580,25 +588,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);
 	}
 
 	/*


[parent: 0cf3d41cbb42]