Re: Document prepared transaction lock waits with two_phase subscriptions

From: Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>
To: Zhijie Hou <houzhijie22(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 09:44:01
Message-ID: CABdArM7m8bP7BMgxkNBVKB3idM4Kqy2eqPgvQ6En5F9foJrNNA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Nov 17, 2026 at 12:29 PM Zhijie Hou <houzhijie22(at)gmail(dot)com> wrote:
>
> Here is the V3 patches for all branches.
>
>

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?

--
Thanks,
Nisha

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2026-11-17 09:51:56 Re: libpq: SCRAM client proofs serialize across threads on OpenSSL 3 (re-init of the HMAC per PBKDF2 iteration)
Previous Message Aleksander Alekseev 2026-11-17 09:37:23 Re: [PATCH] Table sync race with REFRESH PUBLICATION