From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Christophe Pettus <xof@thebuild.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: vaibhave postgres <postgresvaibhave@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Noah Misch <noah@leadboat.com>
Cc: pgsql-bugs@lists.postgresql.org
Cc: vsekar@microsoft.com
Subject: Re: vacuumdb: permission denied for schema "pg_temp_7"
Date: Thu, 26 Sep 2024 23:19:33 +0900
Message-ID: <b54507e4-6fa6-4198-92be-ea9118d131da@oss.nttdata.com> (raw)
In-Reply-To: <ZvNJNoumGREYshTF@paquier.xyz>
References: <Zu3iMzfiGBTbg3iy@nathan>
<20240920215030.8d.nmisch@google.com>
<2603142.1726873660@sss.pgh.pa.us>
<5f37ee56-14fb-44fe-9150-9eb97e10538b@oss.nttdata.com>
<2649165.1726898595@sss.pgh.pa.us>
<CAM_eQjyWnu_LUt6JWjh3boNFHAT9DBrcbamhG3Xy39Q4Ph=2rg@mail.gmail.com>
<ZvGye19tHrhLCpFl@nathan>
<E9660D68-5B26-488E-9AB2-2CA1E8EDDDA1@thebuild.com>
<ZvIRG6282T33q3Ku@paquier.xyz>
<00d98a9f-bec1-4cd0-b59e-1043ae04c8bc@oss.nttdata.com>
<ZvNJNoumGREYshTF@paquier.xyz>
On 2024/09/25 8:20, Michael Paquier wrote:
>> We should probably add a condition like "relpersistence != CppAsString2(RELPERSISTENCE_TEMP)"
>> to the queries in get_parallel_object_list().
>
> Missed your point. An extra filter based on relpersistence can indeed
> make sense for this path.
Patch attached.
Should reindexdb skip temporary tables or indexes even when specified explicitly
with the -t or -i options? Currently, the patch doesn't change this behavior;
reindexdb will still not skip them if specified.
If we agree to back-patch, it should be applied to v13, as parallel mode was
introduced in that version.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:v1-0001-reindexdb-Skip-reindexing-temporary-tables-and-in.patch (text/plain, 1.9K) download
From b2e857eeaa9161aa4308df5158b8f11935cca977 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 26 Sep 2024 21:06:49 +0900
Subject: [PATCH v1] reindexdb: Skip reindexing temporary tables and indexes.
Reindexing temp tables or indexes of other sessions is not allowed.
However, reindexdb in parallel mode previously listed them as
the objects to process, leading to failures.
This commit ensures reindexdb in parallel mode skips temporary tables
and indexes by adding a condition based on the relpersistence column
in pg_class to the object listing queries, preventing these issues.
Note that this commit does not affect reindexdb when temporary tables
or indexes are explicitly specified using the -t or -j options;
reindexdb still does not skip them and can cause an error.
Back-patch to v13 where parallel mode was introduced in reindexdb.
Discussion: https://postgr.es/m/5f37ee56-14fb-44fe-9150-9eb97e10538b@oss.nttdata.com
---
src/bin/scripts/reindexdb.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.cindex 904c196fbb..af0738d933 100644--- a/src/bin/scripts/reindexdb.c+++ b/src/bin/scripts/reindexdb.c@@ -649,6 +649,8 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
" AND c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) ")\n"
+ " AND c.relpersistence != "+ CppAsString2(RELPERSISTENCE_TEMP) "\n"
" ORDER BY c.relpages DESC;");
break;
@@ -671,6 +673,8 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
" WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) ")\n"
+ " AND c.relpersistence != "+ CppAsString2(RELPERSISTENCE_TEMP) "\n"
" AND ns.nspname IN (");
for (cell = user_list->head; cell; cell = cell->next)
--
2.45.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: masao.fujii@oss.nttdata.com, michael@paquier.xyz, xof@thebuild.com, nathandbossart@gmail.com, postgresvaibhave@gmail.com, tgl@sss.pgh.pa.us, noah@leadboat.com, pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com
Subject: Re: vacuumdb: permission denied for schema "pg_temp_7"
In-Reply-To: <b54507e4-6fa6-4198-92be-ea9118d131da@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox