Deadlock concern caused by TRUNCATE on user_catalog_table in synchronous mode
Hi
I've been discussing about user_catalog_table
and the possibility of deadlock during synchronous mode
of logical replication in [1]/messages/by-id/MEYP282MB166933B1AB02B4FE56E82453B64D9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM. I'll launch a new thread
and summarize the contents so that anyone who is
interested in this title can join the discussion.
We don't have any example of user_catalog_tables
in the core code, so any response and idea related to this area is helpful.
Now, we don't disallow output plugin to take a lock
on user_catalog_table. Then, we can consider a deadlock scenario like below.
1. TRUNCATE command is performed on user_catalog_table.
2. TRUNCATE command locks the table and index with ACCESS EXCLUSIVE LOCK.
3. TRUNCATE waits for the subscriber's synchronization
when synchronous_standby_names is set.
4. Here, the walsender hangs, *if* it tries to acquire a lock on the user_catalog_table
because the table where it wants to see is locked by the TRUNCATE already.
(Here, we don't talk about LOCK command because
the discussion is in progress in another thread independently - [2]/messages/by-id/CALDaNm1UB==gL9Poad4ETjfcyGdJBphWEzEZocodnBd--kJpVw@mail.gmail.com)
Another important point here is that we can *not*
know how and when plugin does read only access to user_catalog_table in general,
because it depends on the purpose of the plugin.
Then, I'm thinking that changing a behavior of TRUNCATE side
to error out when TRUNCATE is performed on user_catalog_table
will work to make the concern disappear. Kindly have a look at the attached patch.
[1]: /messages/by-id/MEYP282MB166933B1AB02B4FE56E82453B64D9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM
[2]: /messages/by-id/CALDaNm1UB==gL9Poad4ETjfcyGdJBphWEzEZocodnBd--kJpVw@mail.gmail.com
Best Regards,
Takamichi Osumi
Attachments:
disallow_TRUNCATE_on_user_catalog_table_v01.patchapplication/octet-stream; name=disallow_TRUNCATE_on_user_catalog_table_v01.patchDownload
From 79e7990f9823d02d0f8bab81fcd6e86acb57588d Mon Sep 17 00:00:00 2001
From: Osumi Takamichi <osumi.takamichi@fujitsu.com>
Date: Wed, 19 May 2021 09:06:32 +0000
Subject: [PATCH v1] disallow TRUNCATE on user_catalog_table
When TRUNCATE command is executed on user_catalog_table
in synchronous_mode during logial replication, TRUNCATE waits for
the subscriber's synchronization. At the same time, we don't have
a restriction that output plugin cannot take a lock on it. Therefore,
the walsender can hang due to the deadlock to take a new lock
on a user_catalog_table, since the table is locked by TRUNCATE already.
This patch addresses such a deadlock hazards by prohibiting
an operation to issue TRUNCATE on user_catalog_table.
---
doc/src/sgml/ref/truncate.sgml | 4 ++++
src/backend/commands/tablecmds.c | 13 +++++++++++++
src/test/regress/expected/truncate.out | 5 +++++
src/test/regress/sql/truncate.sql | 5 +++++
4 files changed, 27 insertions(+)
diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml
index 9d846f8..d54b8ab 100644
--- a/doc/src/sgml/ref/truncate.sgml
+++ b/doc/src/sgml/ref/truncate.sgml
@@ -176,6 +176,10 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [
supported by the foreign data wrapper, for instance,
see <xref linkend="postgres-fdw"/>.
</para>
+
+ <para>
+ <command>TRUNCATE</command> on user_catalog_table is not supported.
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ebc6203..7bd8faa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2128,6 +2128,7 @@ static void
truncate_check_rel(Oid relid, Form_pg_class reltuple)
{
char *relname = NameStr(reltuple->relname);
+ Relation relation = NULL;
/*
* Only allow truncate on regular tables, foreign tables using foreign
@@ -2158,6 +2159,18 @@ truncate_check_rel(Oid relid, Form_pg_class reltuple)
errmsg("permission denied: \"%s\" is a system catalog",
relname)));
+ /*
+ * Disallow truncate on user_catalog_table, with attention to the deadlock
+ * scenario that output plugin takes an lock on it in synchronous mode of
+ * logical replication.
+ */
+ relation = RelationIdGetRelation(relid);
+ if (RelationIsUsedAsCatalogTable(relation))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot TRUNCATE an user_catalog_table"));
+ RelationClose(relation);
+
InvokeObjectTruncateHook(relid);
}
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 1e88e86..4948ea5 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -592,3 +592,8 @@ SELECT a as "from table trunc_a" FROM trunc_a ORDER BY a;
(3 rows)
DROP TABLE trunc_a, ref_c;
+-- truncate an user_catalog_table
+CREATE TABLE my_user_catalog_table (col integer) WITH (user_catalog_table = true);
+TRUNCATE my_user_catalog_table; -- should fail
+ERROR: cannot TRUNCATE an user_catalog_table
+DROP TABLE my_user_catalog_table;
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index 54f26e3..413cab8 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -327,3 +327,8 @@ SELECT a as "from table ref_c" FROM ref_c;
SELECT a as "from table trunc_a" FROM trunc_a ORDER BY a;
DROP TABLE trunc_a, ref_c;
+
+-- truncate an user_catalog_table
+CREATE TABLE my_user_catalog_table (col integer) WITH (user_catalog_table = true);
+TRUNCATE my_user_catalog_table; -- should fail
+DROP TABLE my_user_catalog_table;
--
2.2.0