Re: Document prepared transaction lock waits with two_phase subscriptions

From: Zhijie Hou <houzhijie22(at)gmail(dot)com>
To: shveta malik <shveta(dot)malik(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>, "amit(dot)kapila16(at)gmail(dot)com" <amit(dot)kapila16(at)gmail(dot)com>, Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>, shveta malik <shvetamalik(at)gmail(dot)com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: 2026-11-17 06:59:55
Message-ID: e1b1fdae-3a94-4f36-bf72-2f30e5cfd451@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-11-17 07:15:33 Re: Assertion failure with temporary relations in single-user mode
Previous Message Zhijie Hou 2026-11-17 06:58:58 Re: Document prepared transaction lock waits with two_phase subscriptions