From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Zhijie Hou <houzhijie22@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: shveta malik <shveta.malik@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: Mon, 16 Nov 2026 13:44:57 +0000
Message-ID: <avsI2UmGU6U8TQlF@bdtpg> (raw)
In-Reply-To: <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
<avrOjINXdJs1XiwW@bdtpg>
<53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>
Hi,
On Mon, Nov 16, 2026 at 08:20:45PM +0800, Zhijie Hou wrote:
> On Mon, Nov16, 2026 at 05:36 PM Bertrand Drouvot wrote:
> >
> > === 1
> >
> > I wonder if it would also be worth mentioning a nonzero lock_timeout
> > as a possible mitigation too?
>
> Before adding this, I'd like to confirm what would be a good way to suggest
> users configure lock_timeout here. Setting lock_timeout globally (or at the
> database level) would also apply to the logical replication apply worker
> itself (potentially causing it to error out on normal lock waits). On the
> other hand, setting it per session/transaction before running commands that
> take conflicting locks (such as TRUNCATE, ALTER TABLE, LOCK, etc.) avoids
> that, though there are many commands that can take conflicting locks so
> users would need to remember to set it whenever running them (or set it per
> role for administrative sessions). Do you have per-session usage in mind, or
> another way of using lock_timeout?
I had per-session/transaction usage in mind, before executing commands that might
request conflicting table locks. A per role setting also makes sense when a
dedicated role is used for administrative commands. I agree that we should not
recommend setting it globally or at the database level, as that would also affect
the apply workers.
What about juse one sentence, something like?
"
Using a nonzero lock_timeout in sessions or transactions that might request such
conflicting locks can prevent them from waiting indefinitely.
"
> Thanks 2, 3 and 4 look good to me and addressed in V2.
Thanks!
> > Another options I could see (because the doc changes explain the
> > workarounds) would be to help diagnose:
> >
> > 1/ improve log_lock_waits to identify a prepared transaction holding a
> > lock by its XID, instead of reporting it as process 0.
> >
> > 2/ improve the existing pg_locks example query so that it identifies
> > locks held by both newly prepared and recovered prepared transactions.
> >
> > Thoughts?
>
> Both sound like useful diagnostic improvements for prepared transactions in
> general (even outside logical replication), so +1 for discussing them in a
> separate thread.
Thanks for confirming, I'll open a dedicated thread.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
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: bertranddrouvot.pg@gmail.com, houzhijie22@gmail.com, amit.kapila16@gmail.com, nisha.moond412@gmail.com, sawada.mshk@gmail.com, shveta.malik@gmail.com, dilipbalaut@gmail.com
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
In-Reply-To: <avsI2UmGU6U8TQlF@bdtpg>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <avrOjINXdJs1XiwW@bdtpg> <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> <avsI2UmGU6U8TQlF@bdtpg>
* 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