On Mon, Nov 16, 2026 at 6:54 PM Amit Kapila <[email protected]> wrote: > > On Mon, Nov 16, 2026 at 7:14 PM Bertrand Drouvot > <[email protected]> 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.
+1 for documenting this behavior. I think we can backpatch it. Regarding the proposed description, I think it would be better to suggest cancelling the waiting session as the primary way to resolve the situation, and to mention manually rolling back the prepared transaction as an alternative. Cancelling the waiting session always unblocks the apply worker, whereas the latter requires several steps. On the other hand, manually rolling back the prepared transaction requires several steps: disabling the subscription, waiting for the worker to exit, setting two_phase to false, and enabling the subscription again etc.. Also, I think the description should say to disable the subscription first, then roll back the prepared transaction, set two_phase to false, and enable the subscription again as Kuroda-san suggested. It might also be worth noting that the transaction is applied again after the waiting command completes. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
