v2-0001-vacuumdb-Skip-temporary-tables-in-filtering-query.patch

From a234eb7a0ad24a9d5641168346ce608515f484c2 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 24 Sep 2024 10:00:29 +0900
Subject: [PATCH v2] vacuumdb: Skip temporary tables in filtering query

Running vacuumdb with a non-superuser when another user has created a
temporary table would lead to a mid-flight failure.

There are other issues related to schema-level USAGE, which will be
tackled later.

Author: VaibhaveS
Discussion: https://postgr.es/m/CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com
Backpatch-through: 12
---
 src/bin/scripts/vacuumdb.c | 40 ++++++++++++++++++++++++--------------
 1 file changed, 25 insertions(+), 15 deletions(-)

diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 7c33e13e1a..249622068a 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -491,7 +491,6 @@ vacuum_one_database(ConnParams *cparams,
 	int			ntups;
 	bool		failed = false;
 	bool		objects_listed = false;
-	bool		has_where = false;
 	const char *initcmd;
 	const char *stage_commands[] = {
 		"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
@@ -665,7 +664,10 @@ 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 or schemas listed by the user */
+	/*
+	 * Used to match the tables or schemas listed by the user, completing the
+	 * JOIN clause.
+	 */
 	if (objects_listed)
 	{
 		appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
@@ -676,14 +678,26 @@ vacuum_one_database(ConnParams *cparams,
 			appendPQExpBufferStr(&catalog_query, "c.oid\n");
 		else
 			appendPQExpBufferStr(&catalog_query, "ns.oid\n");
+	}
 
+	/*
+	 * Exclude temporary tables, beginning the WHERE clause.
+	 */
+	appendPQExpBufferStr(&catalog_query,
+						 " WHERE c.relpersistence != " CppAsString2(RELPERSISTENCE_TEMP));
+
+	/*
+	 * Used to match the tables or schemas listed by the user, for the WHERE
+	 * clause.
+	 */
+	if (objects_listed)
+	{
 		if (objfilter & OBJFILTER_SCHEMA_EXCLUDE)
 			appendPQExpBuffer(&catalog_query,
-							  " WHERE listed_objects.object_oid IS NULL\n");
+							  " AND listed_objects.object_oid IS NULL\n");
 		else
 			appendPQExpBuffer(&catalog_query,
-							  " WHERE listed_objects.object_oid IS NOT NULL\n");
-		has_where = true;
+							  " AND listed_objects.object_oid IS NOT NULL\n");
 	}
 
 	/*
@@ -695,11 +709,9 @@ vacuum_one_database(ConnParams *cparams,
 	if ((objfilter & OBJFILTER_TABLE) == 0)
 	{
 		appendPQExpBuffer(&catalog_query,
-						  " %s c.relkind OPERATOR(pg_catalog.=) ANY (array["
+						  " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
 						  CppAsString2(RELKIND_RELATION) ", "
-						  CppAsString2(RELKIND_MATVIEW) "])\n",
-						  has_where ? "AND" : "WHERE");
-		has_where = true;
+						  CppAsString2(RELKIND_MATVIEW) "])\n");
 	}
 
 	/*
@@ -712,25 +724,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);
 	}
 
 	/*
-- 
2.45.2