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 1xSvhg-00000002FVk-1Zo3 for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Nov 2026 12:20:56 +0000 Received: from localhost ([127.0.0.1]:45508 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSvhf-00000000yYX-1T26 for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Nov 2026 12:20:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:49470) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSvhf-00000000yYO-04Cf for pgsql-hackers@lists.postgresql.org; Mon, 16 Nov 2026 12:20:55 +0000 Received: from mail-wm2-x06.google.com ([2a00:1450:4864:31::6]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xSvhc-00000001K5L-3qHi for pgsql-hackers@lists.postgresql.org; Mon, 16 Nov 2026 12:20:54 +0000 Received: by mail-wm2-x06.google.com with SMTP id 5b1f17b1804b1-4a77811be24so10706085e9.0 for ; Mon, 16 Nov 2026 04:20:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794831652; x=1795436452; darn=lists.postgresql.org; h=in-reply-to:from:references:cc:to:subject:user-agent:mime-version :date:message-id:content-type:from:to:cc:subject:date:message-id :reply-to:content-type; bh=aDdbvo93EYJagHaV6DN+H0C4s9zw5KE9v+4ulKaOCek=; b=DKDuGFGiQMdg+pFggbRFY2x1OOq0LBF7NlSscNcZbPTTWQhcmcBCIBlwcdFweKEpDP 1RE2+sloFfDT23T3O7nlT5KdvdgbdCLlfEG30yC/1dqPNg2KaEBmpSbNe6vPAkEEhaZs JbsKiXMaaidg4ZDOhT1I3n9CjyUWX19e5PLO7Re75ExIHDdsaz5WjTFVMjcJZC5RqPmO tR017MgV+6Y/uz3MHUF1unEg+lKktG8u451ppIr+MFFrvoMCmtILuNi95Lam/+sRTCbT VJsYe1cr/KPwHRbqpAfN+G3cTThov/7DH2ZsI1K5A79JOrYyl03gELaGJ1RNF8xQ0DKt HmxA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794831652; x=1795436452; h=in-reply-to:from:references:cc:to:subject:user-agent:mime-version :date:message-id:content-type:x-gm-gg:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to:content-type; bh=aDdbvo93EYJagHaV6DN+H0C4s9zw5KE9v+4ulKaOCek=; b=xcFzCejjRgfkT6XpRrmB1BBpSjmIUdCKPeXd1+hLmaNdH9PIXMyPmNlhsmPcpcY76U 36jfq+mK0RU/sp7xiHRwjqt70mILdnb1X16N+oI6gvAujv+zM9p9gibdr3al9Rb/m4n7 Zg0qQ/i16LkZxLu/q826r/ts7gjFMbFCbgbXb1/+X8cqrpqQCJFVJ+quM0AuaFcrqQCb +ZfOyCjPVe4Gli364OcHHdas5XJ4qJnyjW9DFZHbWHDaCAqe/hvzBYJeV31ZfO+I2moX +rVLlzEY9dUaSOJV4JiIABwrIxnHXnWck7aZ8d2a4RPTzBTB4fhlz5Sf8i2QUNVulm2E F4fQ== X-Gm-Message-State: APuU4Z/Rjb0aupTIYxeoX0KaXYA1F3ScWdGcP3ZQuwpEgwXQhBWARTPm ZZheNzHsjwiAl/zK1lJOkYJPCcnxLV8XBBDmT8+ioxIrXw8m7fFIEWSR X-Gm-Gg: AYBFou0i8EQmN/U3Oir784ZfjNmZpAe29rqms02kA47X0KHgN7JpsSSfjqKcqfHYxG0 hGI5Pd2gpmUbJco7dHT+8m9kfTDWPfzZfsd7uPeubRboZnWGwPw8RTMM/W9pkljtVHgvQBXZQX4 E+33S3iFCrkkeUSfT+jxugrUjUlEz9UDzDNuA4ju6kTfVamtUDvf5j5fQWxzqOlqLr1fWg9e9Cf FJijYik3ajQ5yOEmhTEBZ5o7/u5w4cuPrZGHBldS0kJpUM9fqhYfVBgZKhXI9lByJoABNZ8WijP OQ/XCDwlBZ7C+fe4U6efd9H6LURHsCBvc30soHzrLU6o5mAtR5V4z7P7DDZ/qJxIhrCByZxwomh BgCctvcS4/b4PSPC0J/tFjo9oLjdTvDbFtPFIOlmxviVldE3mKxNFrLMEm7d7wz7plMKRBPErC+ byFv7HZomV/8XKSFj52mBcbhjcMqEJTQiPyEyXV3IloHUNDwomUxZusg/R0njeHCCMUnDxOnus5 G51seGQB5/cu/8jVwRnoywWledT/Jay4qqUJu5/nqj27eDVZvaY8WCrZQSddS7NcTcAk6wblIEh KS+rZLsape+fAniS8+6AfnAz+apdtO4J0NOfyBfGwcTsLnOPNZuoYVbduTKZc6ltsMutSDupH5g G6p8bnUb05rF+6DWYJfZaGO+fB5ZT3bcsrc8GlwJSrB6oCqCdUbVYpY5me5BwPw== X-Received: by 2002:a05:600c:3b9d:b0:4bb:5dbf:af06 with SMTP id 5b1f17b1804b1-4bb5dbfb103mr202232355e9.9.1794831651640; Mon, 16 Nov 2026 04:20:51 -0800 (PST) Received: from [17.235.206.45] ([17.235.206.45]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-49c5d22394esm22679509f8f.3.2026.11.16.04.20.48 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Nov 2026 04:20:51 -0800 (PST) Content-Type: multipart/mixed; boundary="------------N5tSUBeIgfgoGUtrqeX0HpzE" Message-ID: <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> Date: Mon, 16 Nov 2026 20:20:45 +0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Document prepared transaction lock waits with two_phase subscriptions To: Bertrand Drouvot Cc: "pgsql-hackers@lists.postgresql.org" , "amit.kapila16@gmail.com" , Nisha Moond , Masahiko Sawada , shveta malik , Dilip Kumar References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> From: Zhijie Hou In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------N5tSUBeIgfgoGUtrqeX0HpzE Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi, On Mon, Nov16, 2026 at 05:36 PM Bertrand Drouvot wrote: > > On Fri, Nov 13, 2026 at 11:58:10AM +0800, Zhijie Hou wrote: > > > > To recover from this state, the user can either cancel the waiting > > local session on the subscriber so the apply worker can proceed, or > > manually roll back the prepared transaction on the subscriber, > > disable two_phase until that transaction is replicated again after > > being committed on the publisher, and then re-enable two_phase if > > required. Considering the existing caution about prepared > > transactions and the available workarounds, we suggest documenting > > this locking hazard and how to resolve it for logical replication as > > well. > > Yeah, +1 for documenting it. > > > Please see the attached patch for the documentation changes. Thanks for reviewing. > > === 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? > > === 2 > > + logical replication apply worker can be completed only by that worker, see > > s/can be completed only by that worker/is normally completed by the subscription's apply worker/? > > === 3 > > + ALTER TABLE), it has to wait, and later lock > + requests on that table > > s/later lock requests/later conflicting lock requests/? > > === 4 > > + The apply worker then cannot reach > + the COMMIT PREPARED or ROLLBACK > + PREPARED that would release the lock, and the sessions > + wait for each other. This is not detected as a deadlock, so > + replication stalls. > > what about? > > or ROLLBACK PREPARED. This situation is not > detected as a deadlock, so replication can remain stalled > indefinitely. Thanks 2, 3 and 4 look good to me and addressed in V2. > > Given these trade-offs, we prefer to document this locking hazard > > and the workarounds for now. > > 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. -- Best Regards, Zhijie Hou --------------N5tSUBeIgfgoGUtrqeX0HpzE Content-Type: text/plain; charset=UTF-8; name="v2-0001-Document-prepared-transaction-lock-waits-with-two.patch" Content-Disposition: attachment; filename*0="v2-0001-Document-prepared-transaction-lock-waits-with-two.pa"; filename*1="tch" Content-Transfer-Encoding: base64 RnJvbSBmYmI1OGQ0MmUwM2JlYmMxMzI4NmNjNTEwMGRiNDI4MGRiZGY1MGYwIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBaaGlqaWUgSG91IDxob3V6aGlqaWUyMkBnbWFpbC5j b20+CkRhdGU6IE1vbiwgMTYgTm92IDIwMjYgMTk6NDY6MDkgKzA4MDAKU3ViamVjdDogW1BB VENIIHYyXSBEb2N1bWVudCBwcmVwYXJlZCB0cmFuc2FjdGlvbiBsb2NrIHdhaXRzIHdpdGgg dHdvX3BoYXNlCiBzdWJzY3JpcHRpb25zLgoKV2hlbiB0d29fcGhhc2UgaXMgZW5hYmxlZCBm b3IgYSBzdWJzY3JpcHRpb24sIGEgdHJhbnNhY3Rpb24gcHJlcGFyZWQgYnkKdGhlIGFwcGx5 IHdvcmtlciBjb250aW51ZXMgdG8gaG9sZCBpdHMgbG9ja3MgdW50aWwgQ09NTUlUIFBSRVBB UkVEIG9yClJPTExCQUNLIFBSRVBBUkVEIGlzIGFwcGxpZWQuIElmIGFub3RoZXIgc2Vzc2lv biByZXF1ZXN0cyBhIGNvbmZsaWN0aW5nCmxvY2sgb24gYSB0YWJsZSBtb2RpZmllZCBieSB0 aGF0IHByZXBhcmVkIHRyYW5zYWN0aW9uLCBpdCB3YWl0cyBiZWhpbmQKdGhlIHByZXBhcmVk IHRyYW5zYWN0aW9uLCBhbmQgc3Vic2VxdWVudCBjb25mbGljdGluZyBsb2NrIHJlcXVlc3Rz IGJ5CnRoZSBhcHBseSB3b3JrZXIgb24gdGhlIHNhbWUgdGFibGUgcXVldWUgYmVoaW5kIHRo YXQgc2Vzc2lvbi4gQmVjYXVzZQp0aGUgcHJlcGFyZWQgdHJhbnNhY3Rpb24gaXMgbm90IHdh aXRpbmcgb24gYW55IGxvY2ssIHRoaXMgc2l0dWF0aW9uIGlzCm5vdCBkZXRlY3RlZCBhcyBh IGRlYWRsb2NrIGFuZCByZXBsaWNhdGlvbiBjYW4gcmVtYWluIHN0YWxsZWQKaW5kZWZpbml0 ZWx5LgoKRG9jdW1lbnQgdGhpcyBjYXZlYXQgYW5kIGhvdyB0byByZWNvdmVyIGZyb20gaXQg dW5kZXIgQ1JFQVRFIFNVQlNDUklQVElPTgphbmQgUFJFUEFSRSBUUkFOU0FDVElPTi4KLS0t CiBkb2Mvc3JjL3NnbWwvcmVmL2NyZWF0ZV9zdWJzY3JpcHRpb24uc2dtbCB8IDIxICsrKysr KysrKysrKysrKysrKysrKwogZG9jL3NyYy9zZ21sL3JlZi9wcmVwYXJlX3RyYW5zYWN0aW9u LnNnbWwgfCAgNiArKysrKy0KIDIgZmlsZXMgY2hhbmdlZCwgMjYgaW5zZXJ0aW9ucygrKSwg MSBkZWxldGlvbigtKQoKZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9yZWYvY3JlYXRlX3N1 YnNjcmlwdGlvbi5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9jcmVhdGVfc3Vic2NyaXB0aW9u LnNnbWwKaW5kZXggNjI0OGMyMjhhNjQuLmM0YzZmNDdmZTJhIDEwMDY0NAotLS0gYS9kb2Mv c3JjL3NnbWwvcmVmL2NyZWF0ZV9zdWJzY3JpcHRpb24uc2dtbAorKysgYi9kb2Mvc3JjL3Nn bWwvcmVmL2NyZWF0ZV9zdWJzY3JpcHRpb24uc2dtbApAQCAtNDI0LDYgKzQyNCwyNyBAQCBD UkVBVEUgU1VCU0NSSVBUSU9OIDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1ldGVyIj5zdWJz Y3JpcHRpb25fbmFtZTwvcmVwbGFjZWFibAogICAgICAgICAgIGFyZSB0aGVuIHByb2Nlc3Nl ZCBpbW1lZGlhdGVseSBieSB0aGUgc3Vic2NyaWJlci4KICAgICAgICAgIDwvcGFyYT4KIAor ICAgICAgICAgPHBhcmE+CisgICAgICAgICAgQSBwcmVwYXJlZCB0cmFuc2FjdGlvbiBhcHBs aWVkIG9uIHRoZSBzdWJzY3JpYmVyIGtlZXBzIGl0cyBsb2NrcworICAgICAgICAgIHVudGls IHRoZSBhcHBseSB3b3JrZXIgcHJvY2Vzc2VzIHRoZSBjb3JyZXNwb25kaW5nCisgICAgICAg ICAgPGNvbW1hbmQ+Q09NTUlUIFBSRVBBUkVEPC9jb21tYW5kPiBvciA8Y29tbWFuZD5ST0xM QkFDSworICAgICAgICAgIFBSRVBBUkVEPC9jb21tYW5kPi4gIElmIGFub3RoZXIgc2Vzc2lv biByZXF1ZXN0cyBhIGNvbmZsaWN0aW5nIGxvY2sKKyAgICAgICAgICBvbiBhIHRhYmxlIG1v ZGlmaWVkIGJ5IHN1Y2ggYSB0cmFuc2FjdGlvbiAoZm9yIGV4YW1wbGUsIHdpdGgKKyAgICAg ICAgICA8Y29tbWFuZD5MT0NLPC9jb21tYW5kPiwgPGNvbW1hbmQ+VFJVTkNBVEU8L2NvbW1h bmQ+LCBvcgorICAgICAgICAgIDxjb21tYW5kPkFMVEVSIFRBQkxFPC9jb21tYW5kPiksIGl0 IGhhcyB0byB3YWl0LCBhbmQgbGF0ZXIKKyAgICAgICAgICBjb25mbGljdGluZyBsb2NrIHJl cXVlc3RzIG9uIHRoYXQgdGFibGUsIGluY2x1ZGluZyB0aGUgYXBwbHkKKyAgICAgICAgICB3 b3JrZXIncyBvd24gbmV4dCBjaGFuZ2UgdG8gaXQsIHF1ZXVlIGJlaGluZCBpdC4gIFRoZSBh cHBseSB3b3JrZXIKKyAgICAgICAgICB0aGVuIGNhbm5vdCByZWFjaCB0aGUgPGNvbW1hbmQ+ Q09NTUlUIFBSRVBBUkVEPC9jb21tYW5kPiBvcgorICAgICAgICAgIDxjb21tYW5kPlJPTExC QUNLIFBSRVBBUkVEPC9jb21tYW5kPi4gIFRoaXMgc2l0dWF0aW9uIGlzIG5vdAorICAgICAg ICAgIGRldGVjdGVkIGFzIGEgZGVhZGxvY2ssIHNvIHJlcGxpY2F0aW9uIGNhbiByZW1haW4g c3RhbGxlZAorICAgICAgICAgIGluZGVmaW5pdGVseS4gIFRvIHJlc29sdmUgdGhpcywgZWl0 aGVyIGNhbmNlbCB0aGUgd2FpdGluZyBzZXNzaW9uLAorICAgICAgICAgIG9yIG1hbnVhbGx5 IHJvbGwgYmFjayB0aGUgcHJlcGFyZWQgdHJhbnNhY3Rpb24gb24gdGhlIHN1YnNjcmliZXIs CisgICAgICAgICAgZGlzYWJsZSA8bGl0ZXJhbD50d29fcGhhc2U8L2xpdGVyYWw+IChzZWUK KyAgICAgICAgICA8bGluayBsaW5rZW5kPSJzcWwtYWx0ZXJzdWJzY3JpcHRpb24tcGFyYW1z LXNldCI+PGxpdGVyYWw+QUxURVIgU1VCU0NSSVBUSU9OIC4uLiBTRVQ8L2xpdGVyYWw+PC9s aW5rPikKKyAgICAgICAgICB1bnRpbCB0aGF0IHRyYW5zYWN0aW9uIGlzIGNvbW1pdHRlZCBv biB0aGUgcHVibGlzaGVyIGFuZCByZXBsaWNhdGVkCisgICAgICAgICAgYWdhaW4sIGFuZCB0 aGVuIHJlLWVuYWJsZSA8bGl0ZXJhbD50d29fcGhhc2U8L2xpdGVyYWw+IGlmIHJlcXVpcmVk LgorICAgICAgICAgPC9wYXJhPgorCiAgICAgICAgICA8cGFyYT4KICAgICAgICAgICBUaGUg aW1wbGVtZW50YXRpb24gb2YgdHdvLXBoYXNlIGNvbW1pdCByZXF1aXJlcyB0aGF0IHJlcGxp Y2F0aW9uCiAgICAgICAgICAgaGFzIHN1Y2Nlc3NmdWxseSBmaW5pc2hlZCB0aGUgaW5pdGlh bCB0YWJsZSBzeW5jaHJvbml6YXRpb24KZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9yZWYv cHJlcGFyZV90cmFuc2FjdGlvbi5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9wcmVwYXJlX3Ry YW5zYWN0aW9uLnNnbWwKaW5kZXggZjRmNjExOGFjMzEuLmE5Nzc4ODY2NDk2IDEwMDY0NAot LS0gYS9kb2Mvc3JjL3NnbWwvcmVmL3ByZXBhcmVfdHJhbnNhY3Rpb24uc2dtbAorKysgYi9k b2Mvc3JjL3NnbWwvcmVmL3ByZXBhcmVfdHJhbnNhY3Rpb24uc2dtbApAQCAtMTMyLDcgKzEz MiwxMSBAQCBQUkVQQVJFIFRSQU5TQUNUSU9OIDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1l dGVyIj50cmFuc2FjdGlvbl9pZDwvcmVwbGFjZWFibGU+CiAgICAgY29udGludWVzIHRvIGhv bGQgd2hhdGV2ZXIgbG9ja3MgaXQgaGVsZC4gIFRoZSBpbnRlbmRlZCB1c2FnZSBvZiB0aGUK ICAgICBmZWF0dXJlIGlzIHRoYXQgYSBwcmVwYXJlZCB0cmFuc2FjdGlvbiB3aWxsIG5vcm1h bGx5IGJlIGNvbW1pdHRlZCBvcgogICAgIHJvbGxlZCBiYWNrIGFzIHNvb24gYXMgYW4gZXh0 ZXJuYWwgdHJhbnNhY3Rpb24gbWFuYWdlciBoYXMgdmVyaWZpZWQgdGhhdAotICAgIG90aGVy IGRhdGFiYXNlcyBhcmUgYWxzbyBwcmVwYXJlZCB0byBjb21taXQuCisgICAgb3RoZXIgZGF0 YWJhc2VzIGFyZSBhbHNvIHByZXBhcmVkIHRvIGNvbW1pdC4gIEEgdHJhbnNhY3Rpb24gcHJl cGFyZWQgYnkgYQorICAgIGxvZ2ljYWwgcmVwbGljYXRpb24gYXBwbHkgd29ya2VyIGlzIG5v cm1hbGx5IGNvbXBsZXRlZCBieSB0aGUKKyAgICBzdWJzY3JpcHRpb24ncyBhcHBseSB3b3Jr ZXI7IHNlZQorICAgIDx4cmVmIGxpbmtlbmQ9InNxbC1jcmVhdGVzdWJzY3JpcHRpb24tcGFy YW1zLXdpdGgtdHdvLXBoYXNlIi8+IGZvciBhCisgICAgbG9jayB3YWl0IHRoaXMgY2FuIGNh dXNlLgogICAgPC9wYXJhPgogCiAgICA8cGFyYT4KLS0gCjIuNTQuMCAoQXBwbGUgR2l0LTE1 NykKCg== --------------N5tSUBeIgfgoGUtrqeX0HpzE--