Skip to content

Commit 3cb828d

Browse files
author
Amit Kapila
committed
Document a few caveats in synchronous logical replication.
In a synchronous logical setup, locking [user] catalog tables can cause deadlock. This is because logical decoding of transactions can lock catalog tables to access them so exclusively locking those in transactions can lead to deadlock. To avoid this users must refrain from having exclusive locks on catalog tables. Author: Takamichi Osumi Reviewed-by: Vignesh C, Amit Kapila Backpatch-through: 9.6 Discussion: https://www.postgresql.org/message-id/20210222222847.tpnb6eg3yiykzpky%40alap3.anarazel.de
1 parent 131ea3e commit 3cb828d

File tree

1 file changed

+78
-22
lines changed

1 file changed

+78
-22
lines changed

‎doc/src/sgml/logicaldecoding.sgml

Lines changed: 78 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1066,28 +1066,83 @@ OutputPluginWrite(ctx, true);
10661066

10671067
<sect1 id="logicaldecoding-synchronous">
10681068
<title>Synchronous Replication Support for Logical Decoding</title>
1069+
<sect2>
1070+
<title>Overview</title>
10691071

1070-
<para>
1071-
Logical decoding can be used to build
1072-
<link linkend="synchronous-replication">synchronous
1073-
replication</link> solutions with the same user interface as synchronous
1074-
replication for <link linkend="streaming-replication">streaming
1075-
replication</link>. To do this, the streaming replication interface
1076-
(see <xref linkend="logicaldecoding-walsender"/>) must be used to stream out
1077-
data. Clients have to send <literal>Standby status update (F)</literal>
1078-
(see <xref linkend="protocol-replication"/>) messages, just like streaming
1079-
replication clients do.
1080-
</para>
1081-
1082-
<note>
10831072
<para>
1084-
A synchronous replica receiving changes via logical decoding will work in
1085-
the scope of a single database. Since, in contrast to
1086-
that, <parameter>synchronous_standby_names</parameter> currently is
1087-
server wide, this means this technique will not work properly if more
1088-
than one database is actively used.
1073+
Logical decoding can be used to build
1074+
<link linkend="synchronous-replication">synchronous
1075+
replication</link> solutions with the same user interface as synchronous
1076+
replication for <link linkend="streaming-replication">streaming
1077+
replication</link>. To do this, the streaming replication interface
1078+
(see <xref linkend="logicaldecoding-walsender"/>) must be used to stream out
1079+
data. Clients have to send <literal>Standby status update (F)</literal>
1080+
(see <xref linkend="protocol-replication"/>) messages, just like streaming
1081+
replication clients do.
1082+
</para>
1083+
1084+
<note>
1085+
<para>
1086+
A synchronous replica receiving changes via logical decoding will work in
1087+
the scope of a single database. Since, in contrast to
1088+
that, <parameter>synchronous_standby_names</parameter> currently is
1089+
server wide, this means this technique will not work properly if more
1090+
than one database is actively used.
10891091
</para>
1090-
</note>
1092+
</note>
1093+
</sect2>
1094+
1095+
<sect2 id="logicaldecoding-synchronous-caveats">
1096+
<title>Caveats</title>
1097+
1098+
<para>
1099+
In synchronous replication setup, a deadlock can happen, if the transaction
1100+
has locked [user] catalog tables exclusively. This is because logical decoding of
1101+
transactions can lock catalog tables to access them. To avoid this users
1102+
must refrain from taking an exclusive lock on [user] catalog tables. This can
1103+
happen in the following ways:
1104+
1105+
<itemizedlist>
1106+
<listitem>
1107+
<para>
1108+
Issuing an explicit <command>LOCK</command> on <structname>pg_class</structname>
1109+
(or any other catalog table) in a transaction.
1110+
</para>
1111+
</listitem>
1112+
1113+
<listitem>
1114+
<para>
1115+
Perform <command>CLUSTER</command> on <structname>pg_class</structname> in
1116+
a transaction.
1117+
</para>
1118+
</listitem>
1119+
1120+
<listitem>
1121+
<para>
1122+
<command>PREPARE TRANSACTION</command> after <command>LOCK</command> command
1123+
on <structname>pg_class</structname> and allow logical decoding of two-phase
1124+
transactions.
1125+
</para>
1126+
</listitem>
1127+
1128+
<listitem>
1129+
<para>
1130+
<command>PREPARE TRANSACTION</command> after <command>CLUSTER</command>
1131+
command on <structname>pg_trigger</structname> and allow logical decoding of
1132+
two-phase transactions. This will lead to deadlock only when published table
1133+
have a trigger.
1134+
</para>
1135+
</listitem>
1136+
1137+
<listitem>
1138+
<para>
1139+
Executing <command>TRUNCATE</command> on [user] catalog table in a
1140+
transaction.
1141+
</para>
1142+
</listitem>
1143+
</itemizedlist>
1144+
</para>
1145+
</sect2>
10911146
</sect1>
10921147

10931148
<sect1 id="logicaldecoding-streaming">
@@ -1253,9 +1308,10 @@ stream_commit_cb(...); &lt;-- commit of the streamed transaction
12531308
<para>
12541309
The logical replication solution that builds distributed two phase commit
12551310
using this feature can deadlock if the prepared transaction has locked
1256-
[user] catalog tables exclusively. They need to inform users to not have
1257-
locks on catalog tables (via explicit <command>LOCK</command> command) in
1258-
such transactions.
1311+
[user] catalog tables exclusively. To avoid this users must refrain from
1312+
having locks on catalog tables (e.g. explicit <command>LOCK</command> command)
1313+
in such transactions.
1314+
(See <xref linkend="logicaldecoding-synchronous-caveats"/> for the details.)
12591315
</para>
12601316
</listitem>
12611317
</itemizedlist>

0 commit comments

Comments
 (0)