pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zhijie Hou <houzhijie22@gmail.com>
To: Nisha Moond <nisha.moond412@gmail.com>
Cc: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: amit.kapila16@gmail.com <amit.kapila16@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: Tue, 17 Nov 2026 20:20:00 +0800
Message-ID: <2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.com> (raw)
In-Reply-To: <CABdArM7m8bP7BMgxkNBVKB3idM4Kqy2eqPgvQ6En5F9foJrNNA@mail.gmail.com>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
	<avrOjINXdJs1XiwW@bdtpg>
	<53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>
	<OS7PR01MB18317C2FC8C8A44608F846474F57B2@OS7PR01MB18317.jpnprd01.prod.outlook.com>
	<51a90195-3a5c-429b-a978-e639df8bc225@gmail.com>
	<CABdArM7m8bP7BMgxkNBVKB3idM4Kqy2eqPgvQ6En5F9foJrNNA@mail.gmail.com>

Hi,

On Tue, Nov17, 2026 at 05:44 PM Nisha Moond wrote:
> On Tue, Nov 17, 2026 at 12:29 PM Zhijie Hou <houzhijie22@gmail.com> wrote:
> >
>
> Thanks for the patch. +1 for documenting this. The doc change looks
> good to me. I have one suggestion:
>
> +          and re-enable the subscription.  Once the transaction is finished
> +          on the publisher and replicated again, <literal>two_phase</literal>
> +          can be re-enabled if required.
>
> I don't think we need to wait for the prepared transaction to finish
> on the publisher. two_phase can be re-enabled right after it is
> disabled, while the subscription is still disabled, and the
> transaction is still replicated correctly when the publisher commits
> it. (Confirmed by test)
>
> This works because re-enabling two_phase sets the slot's two_phase_at
> to where replication resumes. The subscriber had already applied the
> PREPARE, so that point is after it. The publisher then sends the whole
> transaction at commit time.
> So the steps could be:
>
>             Alternatively, disable the subscription, manually roll back the
> -          prepared transaction on the subscriber, disable
> +          prepared transaction on the subscriber, disable and then re-enable
>             <literal>two_phase</literal> (see
>             <link
> linkend="sql-altersubscription-params-set"><literal>ALTER SUBSCRIPTION
> ... SET</literal></link>),
> -          and re-enable the subscription.  Once the transaction is finished
> -          on the publisher and replicated again, <literal>two_phase</literal>
> -          can be re-enabled if required.
> +          and re-enable the subscription.  The transaction is replicated again
> +          when it is committed on the publisher.
>            </para>
>
> Thoughts?

Agreed. Here is the updated patches.


-- 
Best Regards,
Zhijie Hou

Attachments: download all (2 files, .tar.gz)

v4-0001-Document-prepared-transaction-lock-waits-with-two-HEAD-PG18.patch (text/plain, 4.4K) download From 9c4bfb874b6e49f3664bcdfdf67e825b74451793 Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Tue, 17 Nov 2026 17:51:01 +0800 Subject: [PATCH v4] Document prepared transaction lock waits with two_phase subscriptions. When two_phase is enabled for a subscription, a transaction prepared by the apply worker continues to hold its locks until COMMIT PREPARED or ROLLBACK PREPARED is applied. If another session requests a conflicting lock on a table modified by that prepared transaction, it waits behind the prepared transaction, and subsequent conflicting lock requests by the apply worker on the same table queue behind that session. Because the prepared transaction is not waiting on any lock, this situation is not detected as a deadlock and replication can remain stalled indefinitely. Document this caveat and how to recover from it under CREATE SUBSCRIPTION and PREPARE TRANSACTION. Author: Zhijie Hou <houzhijie22@gmail.com> Reviewed-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com> Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Nisha Moond <nisha.moond412@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com Backpatch-through: 15 --- doc/src/sgml/ref/create_subscription.sgml | 22 ++++++++++++++++++++++ doc/src/sgml/ref/prepare_transaction.sgml | 6 +++++- 2 files changed, 27 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 6248c228a64..92552e97009 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -424,6 +424,28 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl are then processed immediately by the subscriber. </para> + <para> + A prepared transaction applied on the subscriber keeps its locks + until the apply worker processes the corresponding + <command>COMMIT PREPARED</command> or <command>ROLLBACK + PREPARED</command>. If another session requests a conflicting lock + on a table modified by such a transaction (for example, with + <command>LOCK</command>, <command>TRUNCATE</command>, or + <command>ALTER TABLE</command>), it has to wait, and later + conflicting lock requests on that table, including the apply + worker's own next change to it, queue behind it. The apply worker + then cannot reach the <command>COMMIT PREPARED</command> or + <command>ROLLBACK PREPARED</command>. This situation is not + detected as a deadlock, so replication can remain stalled + indefinitely. To resolve this, cancel the waiting session. + Alternatively, disable the subscription, manually roll back the + prepared transaction on the subscriber, disable and then re-enable + <literal>two_phase</literal> (see + <link linkend="sql-altersubscription-params-set"><literal>ALTER SUBSCRIPTION ... SET</literal></link>), + and re-enable the subscription. The transaction is replicated again + when it is committed on the publisher. + </para> + <para> The implementation of two-phase commit requires that replication has successfully finished the initial table synchronization diff --git a/doc/src/sgml/ref/prepare_transaction.sgml b/doc/src/sgml/ref/prepare_transaction.sgml index f4f6118ac31..a9778866496 100644 --- a/doc/src/sgml/ref/prepare_transaction.sgml +++ b/doc/src/sgml/ref/prepare_transaction.sgml @@ -132,7 +132,11 @@ PREPARE TRANSACTION <replaceable class="parameter">transaction_id</replaceable> continues to hold whatever locks it held. The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that - other databases are also prepared to commit. + other databases are also prepared to commit. A transaction prepared by a + logical replication apply worker is normally completed by the + subscription's apply worker; see + <xref linkend="sql-createsubscription-params-with-two-phase"/> for a + lock wait this can cause. </para> <para> -- 2.54.0 (Apple Git-157)
v4-0001-Document-prepared-transaction-lock-waits-with-two-PG15-PG17.patch (text/plain, 4.0K) download From e189f3d05020b898bb91fbba8bb60503e80f2e90 Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Tue, 17 Nov 2026 17:03:44 +0800 Subject: [PATCH v4] Document prepared transaction lock waits with two_phase subscriptions. When two_phase is enabled for a subscription, a transaction prepared by the apply worker continues to hold its locks until COMMIT PREPARED or ROLLBACK PREPARED is applied. If another session requests a conflicting lock on a table modified by that prepared transaction, it waits behind the prepared transaction, and subsequent conflicting lock requests by the apply worker on the same table queue behind that session. Because the prepared transaction is not waiting on any lock, this situation is not detected as a deadlock and replication can remain stalled indefinitely. Document this caveat and how to recover from it under CREATE SUBSCRIPTION and PREPARE TRANSACTION. Author: Zhijie Hou <houzhijie22@gmail.com> Reviewed-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com> Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Nisha Moond <nisha.moond412@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com Backpatch-through: 15 --- doc/src/sgml/ref/create_subscription.sgml | 16 ++++++++++++++++ doc/src/sgml/ref/prepare_transaction.sgml | 6 +++++- 2 files changed, 21 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index c9c8dd440dc..3d18ad60458 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -343,6 +343,22 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl are then processed immediately by the subscriber. </para> + <para> + A prepared transaction applied on the subscriber keeps its locks + until the apply worker processes the corresponding + <command>COMMIT PREPARED</command> or <command>ROLLBACK + PREPARED</command>. If another session requests a conflicting lock + on a table modified by such a transaction (for example, with + <command>LOCK</command>, <command>TRUNCATE</command>, or + <command>ALTER TABLE</command>), it has to wait, and later + conflicting lock requests on that table, including the apply + worker's own next change to it, queue behind it. The apply worker + then cannot reach the <command>COMMIT PREPARED</command> or + <command>ROLLBACK PREPARED</command>. This situation is not + detected as a deadlock, so replication can remain stalled + indefinitely until the waiting session is canceled. + </para> + <para> The implementation of two-phase commit requires that replication has successfully finished the initial table synchronization diff --git a/doc/src/sgml/ref/prepare_transaction.sgml b/doc/src/sgml/ref/prepare_transaction.sgml index f4f6118ac31..c3848fbc72c 100644 --- a/doc/src/sgml/ref/prepare_transaction.sgml +++ b/doc/src/sgml/ref/prepare_transaction.sgml @@ -132,7 +132,11 @@ PREPARE TRANSACTION <replaceable class="parameter">transaction_id</replaceable> continues to hold whatever locks it held. The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that - other databases are also prepared to commit. + other databases are also prepared to commit. A transaction prepared by a + logical replication apply worker is normally completed by the + subscription's apply worker; see the <literal>two_phase</literal> + parameter of <xref linkend="sql-createsubscription"/> for a lock wait + this can cause. </para> <para> -- 2.54.0 (Apple Git-157)

view thread (14+ messages)  latest in thread

Message-ID: <2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.com>
Permalink:  ../2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.com/
Also on:    postgresql.org/message-id/2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.com

 ·  · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: houzhijie22@gmail.com, nisha.moond412@gmail.com, kuroda.hayato@fujitsu.com, bertranddrouvot.pg@gmail.com, amit.kapila16@gmail.com, sawada.mshk@gmail.com, shveta.malik@gmail.com, dilipbalaut@gmail.com
  Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
  In-Reply-To: <2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.com>
  References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <avrOjINXdJs1XiwW@bdtpg> <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> <OS7PR01MB18317C2FC8C8A44608F846474F57B2@OS7PR01MB18317.jpnprd01.prod.outlook.com> <51a90195-3a5c-429b-a978-e639df8bc225@gmail.com> <CABdArM7m8bP7BMgxkNBVKB3idM4Kqy2eqPgvQ6En5F9foJrNNA@mail.gmail.com> <2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.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