pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zhijie Hou <houzhijie22@gmail.com>
To: shveta malik <shveta.malik@gmail.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: Nisha Moond <nisha.moond412@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
Cc: shveta malik <shvetamalik@gmail.com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: Tue, 17 Nov 2026 14:59:55 +0800
Message-ID: <e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@gmail.com> (raw)
In-Reply-To: <CAJpy0uCdjs5iOYZjOjam237qB-UHMzoqTsdfzgY__Z5scyXmNg@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>
	<CAJpy0uCdjs5iOYZjOjam237qB-UHMzoqTsdfzgY__Z5scyXmNg@mail.gmail.com>

Hi,

On Tue, Nov17, 2026 at 02:26 PM shveta malik wrote:
> I looked at v2 and have a few suggestions, which you can take if you agree.

Thanks for the comments.

> 1)
> + This situation is not
> +          detected as a deadlock, so replication can remain stalled
> +          indefinitely.
>
> How about a little elaborate:
>
> Because the prepared transaction is not waiting on any lock itself,
> this is not detected as a deadlock, and replication can remain stalled
> indefinitely.

I think if a transaction can be prepared, it naturally means it doesn't 
conflict with the others, so I'd keep the current version in the patch.


>
> 2)
>
> +          indefinitely.  To resolve this, either cancel the waiting session,
> +          or manually roll back the prepared transaction on the subscriber,
> +          disable <literal>two_phase</literal> (see
> +          <link
> linkend="sql-altersubscription-params-set"><literal>ALTER SUBSCRIPTION
> ... SET</literal></link>)
> +          until that transaction is committed on the publisher and replicated
> +          again, and then re-enable <literal>two_phase</literal> if required.
> +         </para>
>
>
> --We can separate 2 suggestions, else it becomes difficult to
> understand (at-least I find it difficult).

Agreed.

> --Also we can improve second suggestion a bit.
>
> How about:
>
> To recover, preferably cancel the waiting session, allowing the apply
> worker to proceed and complete the prepared transaction normally.
> Alternatively, execute ROLLBACK PREPARED on the subscriber to release
> the locks. However, this discards the changes made by the prepared
> transaction on the subscriber, so the transaction must be re-applied
> once it is committed on the publisher, to keep the subscriber
> consistent with the publisher. To achieve this, temporarily disable
> two_phase using ALTER SUBSCRIPTION ... SET until the transaction is
> committed on the publisher and replicated to the subscriber, then
> re-enable it.

The proposed text for the second point feels a bit verbose to me. I'd 
prefer to keep it short and clean.


-- 
Best Regards,
Zhijie Hou






view thread (15+ messages)  latest in thread

Message-ID: <e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@gmail.com>
Permalink:  ../e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@gmail.com/
Also on:    postgresql.org/message-id/e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@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, shveta.malik@gmail.com, bertranddrouvot.pg@gmail.com, amit.kapila16@gmail.com, nisha.moond412@gmail.com, sawada.mshk@gmail.com, dilipbalaut@gmail.com, kuroda.hayato@fujitsu.com, shvetamalik@gmail.com
  Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
  In-Reply-To: <e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@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> <CAJpy0uCdjs5iOYZjOjam237qB-UHMzoqTsdfzgY__Z5scyXmNg@mail.gmail.com> <e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@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