Re: Document prepared transaction lock waits with two_phase subscriptions

From: shveta malik <shveta(dot)malik(at)gmail(dot)com>
To: Zhijie Hou <houzhijie22(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:26:30
Message-ID: CAJpy0uCdjs5iOYZjOjam237qB-UHMzoqTsdfzgY__Z5scyXmNg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I looked at v2 and have a few suggestions, which you can take if you agree.

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.

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).
--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.

thanks
Shveta

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2026-11-17 06:45:47 Re: Asynchronous MergeAppend
Previous Message Ayush Tiwari 2026-11-17 05:39:24 Re: Fix two race conditions in processing interrupts