RE: Document prepared transaction lock waits with two_phase subscriptions

From: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: 'Zhijie Hou' <houzhijie22(at)gmail(dot)com>, Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com>
Cc: "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>, 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 05:25:53
Message-ID: OS7PR01MB18317C2FC8C8A44608F846474F57B2@OS7PR01MB18317.jpnprd01.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Thanks for posting the patch. I feel the logging should be improved, but
it might be out-of-scope. Here I focused on the doc patch.

```
+ 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.
```

Isn't it better to disable the subscription before rolling back? If the publisher
runs COMMIT/ROLLBACK PREPARED soon, the resumed apply worker raises
"prepared transaction ... does not exist" error.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Manu 2026-11-17 05:32:27 Use an index scan in RemoveSubscriptionRel()
Previous Message Manu 2026-11-17 05:24:48 REINDEX CONCURRENTLY reports lockers_done > lockers_total