Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSx16-00000002HUC-3bJh for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Nov 2026 13:45:04 +0000 Received: from localhost ([127.0.0.1]:37816 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSx15-00000001Fwu-3PN5 for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Nov 2026 13:45:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:45870) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSx15-00000001Fwm-2B9t for pgsql-hackers@lists.postgresql.org; Mon, 16 Nov 2026 13:45:03 +0000 Received: from mail-wr1-x431.google.com ([2a00:1450:4864:20::431]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xSx14-00000001ECL-12pe for pgsql-hackers@lists.postgresql.org; Mon, 16 Nov 2026 13:45:02 +0000 Received: by mail-wr1-x431.google.com with SMTP id ffacd0b85a97d-48e10a70540so1901096f8f.1 for ; Mon, 16 Nov 2026 05:45:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794836699; x=1795441499; darn=lists.postgresql.org; h=in-reply-to:content-disposition:content-type:mime-version :references:message-id:subject:cc:to:from:date:from:to:cc:subject :date:message-id:reply-to:content-type; bh=s9Ag6jUfOa1OL7UdgmhdibnFJ3T1wC/cS7PMQDS0Smc=; b=dH2EIgVx/FP41wmjIFVZ0pf7se2uVJHGyr6KIz97eNbA2e9qJMW0HafHdbVOM7VRBR lmRPqzV/R+xskSx/dVQqW+yoZUYNixzav3uz+SmLpLm5qJ+Q1II/T5cSWMUEB2TQ3aci aYXQz83/7OayP4ksR1Y1Rt9rFdc2sCaGDSFQrTJ7RBf7s4x33RSQOXDeyhhnWB1d8v8b CjOgMfhjS2Dn0WpzvW5/55eHqYUrLL/PIJATCtQFuol481l6OXRDI/1AVdIjUaEvYGNg 6rZVWtUs2VtlBahv7M8iIVxiHuXa9/iRgymKjs5o5WnV8p3werQg3mFWoL+S3qAJEb0z rLOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794836699; x=1795441499; h=in-reply-to:content-disposition:content-type:mime-version :references:message-id:subject:cc:to:from:date:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=s9Ag6jUfOa1OL7UdgmhdibnFJ3T1wC/cS7PMQDS0Smc=; b=dlkS6/xxXLW5Xxe6fD3anXMWiWIfJ2iYEYL2llFLSUz7OF9LWFR6ufRul1mJcGxqke xmN2nWguQtqsNmFiKjnuaHsFbl3+huvZ03MxhWodvCXvtWKWwIQX1/IZgGj6fLFmYG4u mRCUPBZkWUeTIsBW8YQp1ghlfap4asxC+g53CJC7IbaiqIBV3uqZc2mHzNck4Z11TCOr Tll7nuVuLuViY1cJqvHDc+xdSFhihd0wBuYNokCMQ1Pzu6UdIAuRdLDDxCn8C2EbT1hu aCDRg8hfypr3op7d0oPcwl9dBbje5HyC3cZA5NmuwojK9yunQ8fzFi6VJtGblvyD7ALF jB5w== X-Gm-Message-State: APuU4Z9UFM4hOhRwbqYWedl0G2TButZeuZtRuLVQxZ2pnjnekn+dY1ON 58nE3WFxoajUS/D7z3bmPWo6FRrwRvy9lsK+rBFAttgDHpFIw8UDAvb2 X-Gm-Gg: AZFdTZecBdayfwZzX3AXyec6zNAfi48QNUZuLy8p4fmt0rK1hCbqcO8TngqxN0csFvJ V1wsor6H15nKNIC5ARuOlpe42v8HydxAtaOI0twFJMBCr3+eddtvfSfqUrs3FZRi901NFSo7+ve jFh+6wHbYh5tvmXsEzh8XWZEUyGjMXjrxi74ncgXjRbJXI5EDCvg3FWzxneUb5S8uvaMtHbPTXJ P54vR/Zm6hj46Lb+F7TyxNitnips0nJJoI2/l8zFH7cPOi68sRwbEvE6rLQnH+qK4csCCIjw/ff MsU7wxEVJK3uzh+tLq385e/gqowbB48yol4WKptgwardIy4QfH9dJKhNBe8nbXNx/9UspV6F8jW nVE3IWBi8RP2DxiklaSSecSOokscPXJuEeyaRXGzmDfXnBMffyK1oaiNBEuYcqNbwzHkV9nMqFZ C4ChqiKfQMv82kH5j5nvJTPA3wYcOD0TSSokuIigY7sl26dsrBJGIZjJ41tye3fLrtECCT5wIiE MwgKnShzEIJ289wCl649bzK/h8bIEghgovhy341vQqen4dSTyl3nJ1p/qCfDjcabDaV/c1CpdI4 Ijfp/O65a5DfFej0ATvj74FOqRHiulHYs6cEYzLXO/SX7RK0adyYaRKxHsc5RNOw/CIOhFn7usy IOeY1qIHEuMhI X-Received: by 2002:a05:6000:2489:b0:490:c80e:8e4f with SMTP id ffacd0b85a97d-49c73bf08c2mr24488027f8f.37.1794836699150; Mon, 16 Nov 2026 05:44:59 -0800 (PST) Received: from bdtpg (ec2-15-237-197-144.eu-west-3.compute.amazonaws.com. [15.237.197.144]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-49c6d079a2asm20159297f8f.16.2026.11.16.05.44.58 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 16 Nov 2026 05:44:58 -0800 (PST) Date: Mon, 16 Nov 2026 13:44:57 +0000 From: Bertrand Drouvot To: Zhijie Hou Cc: "pgsql-hackers@lists.postgresql.org" , "amit.kapila16@gmail.com" , Nisha Moond , Masahiko Sawada , shveta malik , Dilip Kumar Subject: Re: Document prepared transaction lock waits with two_phase subscriptions Message-ID: References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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