Re: Document prepared transaction lock waits with two_phase subscriptions

From: Zhijie Hou <houzhijie22(at)gmail(dot)com>
To: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: 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>, Nisha Moond <nisha(dot)moond412(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 07:42:58
Message-ID: 3730ab93-8a56-4e89-b61d-e4ebb9f58877@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

On Tue, Nov17, 2026 at 02:49 PM Masahiko Sawada wrote:
>
> +1 for documenting this behavior. I think we can backpatch it.
>
> Regarding the proposed description, I think it would be better to
> suggest cancelling the waiting session as the primary way to resolve
> the situation, and to mention manually rolling back the prepared
> transaction as an alternative. Cancelling the waiting session always
> unblocks the apply worker, whereas the latter requires several steps.
> On the other hand, manually rolling back the prepared transaction
> requires several steps: disabling the subscription, waiting for the
> worker to exit, setting two_phase to false, and enabling the
> subscription again etc.. Also, I think the description should say to
> disable the subscription first, then roll back the prepared
> transaction, set two_phase to false, and enable the subscription again
> as Kuroda-san suggested. It might also be worth noting that the
> transaction is applied again after the waiting command completes.

Thanks for the comments. I think V3[1] covers the points you mentioned.

what do you think?

[1]
https://www.postgresql.org/message-id/51a90195-3a5c-429b-a978-e639df8bc225%40gmail.com

--
Best Regards,
Zhijie Hou

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jakub Wartak 2026-11-17 07:49:17 Re: [PATCH] basebackup: use sequential access advice for full-file reads
Previous Message Bertrand Drouvot 2026-11-17 07:38:25 Re: pgstat: Flush some statistics within running transactions, take 2