Re: Document prepared transaction lock waits with two_phase subscriptions

From: Zhijie Hou <houzhijie22(at)gmail(dot)com>
To: Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>
Cc: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>, Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "amit(dot)kapila16(at)gmail(dot)com" <amit(dot)kapila16(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, shveta malik <shveta(dot)malik(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: 2026-11-17 12:20:00
Message-ID: 2b66c58e-de72-43ff-b0e1-45174b9ebb8e@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

On Tue, Nov17, 2026 at 05:44 PM Nisha Moond wrote:
> On Tue, Nov 17, 2026 at 12:29 PM Zhijie Hou <houzhijie22(at)gmail(dot)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

Attachment Content-Type Size
v4-0001-Document-prepared-transaction-lock-waits-with-two-HEAD-PG18.patch text/plain 4.4 KB
v4-0001-Document-prepared-transaction-lock-waits-with-two-PG15-PG17.patch text/plain 4.0 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Previous Message mingjie.gmj 2026-11-17 12:04:38 [PATCH] Check domain constraints for NULLs in logical replication