| From: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
|---|---|
| To: | Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com> |
| Cc: | Zhijie Hou <houzhijie22(at)gmail(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, 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 02:53:57 |
| Message-ID: | CAA4eK1+kK_aV4=ajvz1J2vdUpr9UR_ZZ5gM-TJON+vGFjrEbvA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Mon, Nov 16, 2026 at 7:14 PM Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> 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.
> "
I think configuring such a timeout could be tricky for users
especially when the commands are from scripts. We don't
recommend/document such a setting for blocking due to prepared
transactions, for example, I find the following in the docs: "In cases
involving a conflicting prepared transaction, it may not be possible
to make progress until the prepared transaction commits or rolls
back." for serialization failure handling. So, on similar lines we
should try to avoid lock_timeout recommendation here but if you or
others still feel it is helpful then we can consider.
Overall, I agree with you and Hou-san that documenting the lock waits
due to prepared transactions with two_phase enabled subscriptions is a
better approach as we do at other places especially for back-branches.
Even if we decide to do some special lock-detection mechanism for
prepared transactions (by apply worker) as Hou-San mentioned in his
first email, it should be for HEAD but considering the documented
behavior of prepared transactions and no user report, I feel we don't
need to go there.
--
With Regards,
Amit Kapila.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Henson Choi | 2026-11-17 03:02:27 | [DEPARSE] Renamed columns of relations not in FROM break rules and functions |
| Previous Message | Haibo Yan | 2026-11-17 02:35:51 | [PATCH v1] pg_stash_advice: Reject embedded NUL bytes in persistence files |