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 1xRiQf-00000001Ene-02cQ for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Nov 2026 03:58:21 +0000 Received: from localhost ([127.0.0.1]:42824 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRiQd-00000007IZ1-3vCH for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Nov 2026 03:58:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:40772) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRiQd-00000007IYW-23Qv for pgsql-hackers@lists.postgresql.org; Fri, 13 Nov 2026 03:58:19 +0000 Received: from mail-pi2-x0a.google.com ([2607:f8b0:4864:3a::a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xRiQb-00000000ghH-2swi for pgsql-hackers@lists.postgresql.org; Fri, 13 Nov 2026 03:58:18 +0000 Received: by mail-pi2-x0a.google.com with SMTP id 41be03b00d2f7-cd697a57d7eso127858a12.0 for ; Thu, 12 Nov 2026 19:58:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794542296; x=1795147096; darn=lists.postgresql.org; h=subject:cc:to:from:user-agent:mime-version:date:message-id :content-type:from:to:cc:subject:date:message-id:reply-to :content-type; bh=ISVo33O6ygbQwwT/j3UYuWjX/qa4lXPzWaJ4+DcOnJE=; b=vilg83iaV/qiohTHEChHjSsohQ9MhJ/6t7ny/wsDTigOCl5tfDeYNGiXYwilnal/1t 0lRn+Pg9d84mR8Ib5wQyMLt4V3zhzIZ09lE//T/+D+qy3a4NdV+suCLvDE+Ct0BbmtWJ EN0dnBd78g78i6QDku4Vx2DGQcoCEu6fhQO19Zbxl4S/h+meeN8TPevj5yXLeQAVQPGu K1u12/NMAoO+6ebtgVHRiFCZRR3lVYGuV4MzSdtxsqe/ozppkcUjnluILFWQMIVu2GLV 9OA0/i2YpX2C4DrhcbIGWdVW3dStZP9itz2euKVQ37v2tGXMrUHJikkwprC1R76ON0tc GQgg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794542296; x=1795147096; h=subject:cc:to:from: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=ISVo33O6ygbQwwT/j3UYuWjX/qa4lXPzWaJ4+DcOnJE=; b=nMrD7dla0rA2EilzLFivch4CQtDs7VIeONJWIxRFisA8e62IVxMVj3El4bccEFDrva 05hk1PM4gl3pGQivrXa601PqzSOnPoZricSBXrAriILlrHhzrrEsSUp/kVAOzHbmzkVP 6k5qwbynbNGPf/FWsdzSdhDw4AC5ZQWYS5PZrXa4F5IdBNIQ+9q5o3fZ8mseAmO9O3Af C0A2N3+6OB4qpD6MxZFtpZEAkHWkOU6qBwoqMAt7Q9XL7+vzc4LwjaG+bicuiXdUcWTE oP2HQbUFwK+zr5YKRDiqDgMuTIs6CvKXQZIX2pR5vW8/OOQQ6Ge77agx8Udup5xhPxl8 pYHw== X-Gm-Message-State: APuU4Z9Xb4/yWSngaYojqEOIaKWn1Oj8GLzL6rK1y7RgcPmJoNAxMs6p HcLwPYQdCoB6W6fWiJX5TIm4qjayOezjhUe60nIQe0R/9O4oAcxwHG+JHt9MWB/fCkajveTU X-Gm-Gg: AYBFou1E51THDr3Z2gRxIyh3A4HiZzLzciYGcLSsqLEqWZy0Cn2eZZFxQXgjAJQdqnf 5+UkYdGG2cI+1N0GcXHX9aVviXjic4RVjXbPdNEU2gVmmGYYjqjxcoK08Mzo8m5XgTUoBzJAT1w nQm1x1OYviJpXuXoj39MHMUroJb9Wd/uwUGk1JzMPGGz3wvSzsBjFjGHSzHX3rJx1LFYynfgPP6 JcDwN7ZHT1vb+Dkg1mfP76uxDWn+AXkUBfSiDfN0o8bHObeym1D9PHsz7YWJfQQom/wQgS03ra3 NzJqadPBw3RN5jvTEBox2savQuGeoSZZQEsynr4hJFyn1J6uQnR4ghcLeCErOHqY/GhAQEA2yZC hWI8sHd0Zvq43lDknxY702/uR/3pFuNQohT46sHB/KGDJ9R0uDNB/vDJgJ7CGPazfae1bZigK8R 8awCgaUlhVB/UGg+9jLbeOkZKNvrlaBuZBDsNgMgrDh/vEWsD0Q62tu5//NF75lSmra3zuMbMBI wfPyrKuyDSdxkGiq2bLcq0XI/Z5snpT8JWGHK3Zw1QMoEmG7DcBOfyWZFUdGUvi2PGdtZoBJ2n2 rrgUA/o7bbalCkaHGEYWAwS9AHnaW7qFrs6xdmVtxIAXNDKM6RgOHOQA9iVFIcTjrjxs7+d8k8w BTRgCRGUhrlRGjvp4eyykbYnEs0fYCjDl+dJRJbuBrOLWWdGEeKHeB5CBnhaNEx+4zqyGDKV8/3 WyeOshQRbhKspc6ICyK/BzifCyadl9F4qrQek= X-Received: by 2002:a05:6a00:1a94:b0:8b4:4e74:a13e with SMTP id d2e1a72fcca58-8beb1dea213mr1520404b3a.7.1794542296145; Thu, 12 Nov 2026 19:58:16 -0800 (PST) Received: from ?IPV6:2400:79c0:20d:180:9c5a:a591:ccbd:892e? ([2403:300:600d:d80:a917:a591:ccbd:892e]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-8bed1e5ac38sm406367b3a.31.2026.11.12.19.58.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 12 Nov 2026 19:58:15 -0800 (PST) Content-Type: multipart/mixed; boundary="------------stf6Vwhq7gPDO03DhfSbKmdC" Message-ID: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> Date: Fri, 13 Nov 2026 11:58:10 +0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird From: Zhijie Hou To: "pgsql-hackers@lists.postgresql.org" Cc: "amit.kapila16@gmail.com" , Nisha Moond , Masahiko Sawada , shveta malik , Dilip Kumar , Bertrand Drouvot Subject: Document prepared transaction lock waits with two_phase subscriptions 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. --------------stf6Vwhq7gPDO03DhfSbKmdC Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi, While reviewing the prepared-transaction locking hazard involving the conflict log table [1], we noticed that the issue is not specific to the conflict log table and also affects regular user tables. When two_phase is enabled, a prepared transaction on the publisher is also prepared on the subscriber, and like any prepared transaction, it continues to hold its locks until COMMIT PREPARED or ROLLBACK PREPARED. On the subscriber, if a replicated prepared transaction holds a lock on a user table and a local session then tries to acquire a conflicting lock on the same table (e.g., via TRUNCATE, VACUUM FULL, or ALTER TABLE), the local session will block. In this state, if there are further replicated changes on the same table, the apply worker will also be blocked when acquiring the table lock to apply those changes. Because the apply worker cannot advance to subsequent transactions, even if COMMIT PREPARED or ROLLBACK PREPARED is issued on the publisher, the apply worker will not be able to reach and apply it. As a result, replication stalls because the sessions wait for each other, and the deadlock detector does not detect this wait cycle since the prepared transaction is not waiting on any lock. This is similar to the existing caution in the prepared transaction documentation: """ It is unwise to leave transactions in the prepared state for a long time ... Keep in mind also that the transaction continues to hold whatever locks it held. The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that other databases are also prepared to commit. """ 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. Please see the attached patch for the documentation changes. Apart from documenting this, Amit, Nisha, and I also discussed off-list whether the apply worker could detect and resolve this wait cycle automatically. One option we considered is to have the apply worker walk its lock blocking chain once a lock wait exceeds deadlock_timeout. Starting from the lock it is waiting for, the worker would trace conflicting lock holders and conflicting waiters ahead of it in the wait queue (similar to how pg_blocking_pids() finds blockers) to see whether the chain leads back to a prepared transaction of the same subscription, and if so, cancel the session waiting on that prepared transaction (or raise an error if the worker is directly blocked by its own prepared transaction). However, this effectively introduces a separate wait-graph traversal alongside the existing deadlock detector, which seems overly complex for this scenario. We also thought about a much simpler check where, on timeout, the apply worker only inspects the lock it is currently waiting on and treats it as a wait cycle if a prepared transaction of the same subscription also holds a lock on that same table. Unfortunately, that does not cover multi-table cases. For example, suppose a prepared transaction holds a lock on table t1, a local session acquires AccessExclusiveLock on table t2 and then blocks trying to TRUNCATE t1, and the apply worker next tries to apply a change to t2. Because the apply worker is waiting on t2 while the prepared transaction holds the lock on t1, inspecting only t2's lock would miss the cycle. Given these trade-offs, we prefer to document this locking hazard and the workarounds for now. [1] https://www.postgresql.org/message-id/CAJpy0uAWb7hs0271M_NxhOpetAfFcQidrUce5xSOL8jU3Hx1iQ%40mail.gmail.com -- Best Regards, Zhijie Hou --------------stf6Vwhq7gPDO03DhfSbKmdC Content-Type: text/plain; charset=UTF-8; name="v1-0001-Document-prepared-transaction-lock-waits-with-two.patch" Content-Disposition: attachment; filename*0="v1-0001-Document-prepared-transaction-lock-waits-with-two.pa"; filename*1="tch" Content-Transfer-Encoding: base64 RnJvbSBhNTJkNzJlZmEzMjVlZGNlYjY5MjhlNWU4NGE0MDRkNTFhNGUzMmViIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBaaGlqaWUgSG91IDxob3V6aGlqaWUyMkBnbWFpbC5j b20+CkRhdGU6IEZyaSwgMTMgTm92IDIwMjYgMDk6NTg6NDYgKzA4MDAKU3ViamVjdDogW1BB VENIIHYxXSBEb2N1bWVudCBwcmVwYXJlZCB0cmFuc2FjdGlvbiBsb2NrIHdhaXRzIHdpdGgg dHdvX3BoYXNlCiBzdWJzY3JpcHRpb25zLgoKV2hlbiB0d29fcGhhc2UgaXMgZW5hYmxlZCBm b3IgYSBzdWJzY3JpcHRpb24sIGEgdHJhbnNhY3Rpb24gcHJlcGFyZWQgYnkKdGhlIGFwcGx5 IHdvcmtlciBjb250aW51ZXMgdG8gaG9sZCBpdHMgbG9ja3MgdW50aWwgQ09NTUlUIFBSRVBB UkVEIG9yClJPTExCQUNLIFBSRVBBUkVEIGlzIGFwcGxpZWQuIElmIGFub3RoZXIgc2Vzc2lv biByZXF1ZXN0cyBhIGNvbmZsaWN0aW5nCmxvY2sgb24gYSB0YWJsZSBtb2RpZmllZCBieSB0 aGF0IHByZXBhcmVkIHRyYW5zYWN0aW9uLCBpdCB3YWl0cyBiZWhpbmQKdGhlIHByZXBhcmVk IHRyYW5zYWN0aW9uLCBhbmQgc3Vic2VxdWVudCBsb2NrIHJlcXVlc3RzIGJ5IHRoZSBhcHBs eQp3b3JrZXIgb24gdGhlIHNhbWUgdGFibGUgcXVldWUgYmVoaW5kIHRoYXQgc2Vzc2lvbi4g QmVjYXVzZSB0aGUgcHJlcGFyZWQKdHJhbnNhY3Rpb24gaXMgbm90IHdhaXRpbmcgb24gYW55 IGxvY2ssIHRoaXMgd2FpdCBjeWNsZSBpcyBub3QgZGV0ZWN0ZWQKYXMgYSBkZWFkbG9jayBh bmQgcmVwbGljYXRpb24gc3RhbGxzLgoKRG9jdW1lbnQgdGhpcyBjYXZlYXQgYW5kIGhvdyB0 byByZWNvdmVyIGZyb20gaXQgdW5kZXIgQ1JFQVRFIFNVQlNDUklQVElPTgphbmQgUFJFUEFS RSBUUkFOU0FDVElPTi4KLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL2NyZWF0ZV9zdWJzY3JpcHRp b24uc2dtbCB8IDIxICsrKysrKysrKysrKysrKysrKysrKwogZG9jL3NyYy9zZ21sL3JlZi9w cmVwYXJlX3RyYW5zYWN0aW9uLnNnbWwgfCAgNSArKysrLQogMiBmaWxlcyBjaGFuZ2VkLCAy NSBpbnNlcnRpb25zKCspLCAxIGRlbGV0aW9uKC0pCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9z Z21sL3JlZi9jcmVhdGVfc3Vic2NyaXB0aW9uLnNnbWwgYi9kb2Mvc3JjL3NnbWwvcmVmL2Ny ZWF0ZV9zdWJzY3JpcHRpb24uc2dtbAppbmRleCA2MjQ4YzIyOGE2NC4uYjJlNmI4ZDkzNjAg MTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9yZWYvY3JlYXRlX3N1YnNjcmlwdGlvbi5zZ21s CisrKyBiL2RvYy9zcmMvc2dtbC9yZWYvY3JlYXRlX3N1YnNjcmlwdGlvbi5zZ21sCkBAIC00 MjQsNiArNDI0LDI3IEBAIENSRUFURSBTVUJTQ1JJUFRJT04gPHJlcGxhY2VhYmxlIGNsYXNz PSJwYXJhbWV0ZXIiPnN1YnNjcmlwdGlvbl9uYW1lPC9yZXBsYWNlYWJsCiAgICAgICAgICAg YXJlIHRoZW4gcHJvY2Vzc2VkIGltbWVkaWF0ZWx5IGJ5IHRoZSBzdWJzY3JpYmVyLgogICAg ICAgICAgPC9wYXJhPgogCisgICAgICAgICA8cGFyYT4KKyAgICAgICAgICBBIHByZXBhcmVk IHRyYW5zYWN0aW9uIGFwcGxpZWQgb24gdGhlIHN1YnNjcmliZXIga2VlcHMgaXRzIGxvY2tz CisgICAgICAgICAgdW50aWwgdGhlIGFwcGx5IHdvcmtlciBwcm9jZXNzZXMgdGhlIGNvcnJl c3BvbmRpbmcKKyAgICAgICAgICA8Y29tbWFuZD5DT01NSVQgUFJFUEFSRUQ8L2NvbW1hbmQ+ IG9yIDxjb21tYW5kPlJPTExCQUNLCisgICAgICAgICAgUFJFUEFSRUQ8L2NvbW1hbmQ+LiAg SWYgYW5vdGhlciBzZXNzaW9uIHJlcXVlc3RzIGEgY29uZmxpY3RpbmcgbG9jaworICAgICAg ICAgIG9uIGEgdGFibGUgbW9kaWZpZWQgYnkgc3VjaCBhIHRyYW5zYWN0aW9uIChmb3IgZXhh bXBsZSwgd2l0aAorICAgICAgICAgIDxjb21tYW5kPkxPQ0s8L2NvbW1hbmQ+LCA8Y29tbWFu ZD5UUlVOQ0FURTwvY29tbWFuZD4sIG9yCisgICAgICAgICAgPGNvbW1hbmQ+QUxURVIgVEFC TEU8L2NvbW1hbmQ+KSwgaXQgaGFzIHRvIHdhaXQsIGFuZCBsYXRlciBsb2NrCisgICAgICAg ICAgcmVxdWVzdHMgb24gdGhhdCB0YWJsZSwgaW5jbHVkaW5nIHRoZSBhcHBseSB3b3JrZXIn cyBvd24gbmV4dAorICAgICAgICAgIGNoYW5nZSB0byBpdCwgcXVldWUgYmVoaW5kIGl0LiAg VGhlIGFwcGx5IHdvcmtlciB0aGVuIGNhbm5vdCByZWFjaAorICAgICAgICAgIHRoZSA8Y29t bWFuZD5DT01NSVQgUFJFUEFSRUQ8L2NvbW1hbmQ+IG9yIDxjb21tYW5kPlJPTExCQUNLCisg ICAgICAgICAgUFJFUEFSRUQ8L2NvbW1hbmQ+IHRoYXQgd291bGQgcmVsZWFzZSB0aGUgbG9j aywgYW5kIHRoZSBzZXNzaW9ucworICAgICAgICAgIHdhaXQgZm9yIGVhY2ggb3RoZXIuICBU aGlzIGlzIG5vdCBkZXRlY3RlZCBhcyBhIGRlYWRsb2NrLCBzbworICAgICAgICAgIHJlcGxp Y2F0aW9uIHN0YWxscy4gIFRvIHJlc29sdmUgdGhpcywgZWl0aGVyIGNhbmNlbCB0aGUgd2Fp dGluZworICAgICAgICAgIHNlc3Npb24sIG9yIG1hbnVhbGx5IHJvbGwgYmFjayB0aGUgcHJl cGFyZWQgdHJhbnNhY3Rpb24gb24gdGhlCisgICAgICAgICAgc3Vic2NyaWJlciwgZGlzYWJs ZSA8bGl0ZXJhbD50d29fcGhhc2U8L2xpdGVyYWw+IChzZWUKKyAgICAgICAgICA8bGluayBs aW5rZW5kPSJzcWwtYWx0ZXJzdWJzY3JpcHRpb24tcGFyYW1zLXNldCI+PGxpdGVyYWw+QUxU RVIgU1VCU0NSSVBUSU9OIC4uLiBTRVQ8L2xpdGVyYWw+PC9saW5rPikKKyAgICAgICAgICB1 bnRpbCB0aGF0IHRyYW5zYWN0aW9uIGlzIGNvbW1pdHRlZCBvbiB0aGUgcHVibGlzaGVyIGFu ZCByZXBsaWNhdGVkCisgICAgICAgICAgYWdhaW4sIGFuZCB0aGVuIHJlLWVuYWJsZSA8bGl0 ZXJhbD50d29fcGhhc2U8L2xpdGVyYWw+IGlmIHJlcXVpcmVkLgorICAgICAgICAgPC9wYXJh PgorCiAgICAgICAgICA8cGFyYT4KICAgICAgICAgICBUaGUgaW1wbGVtZW50YXRpb24gb2Yg dHdvLXBoYXNlIGNvbW1pdCByZXF1aXJlcyB0aGF0IHJlcGxpY2F0aW9uCiAgICAgICAgICAg aGFzIHN1Y2Nlc3NmdWxseSBmaW5pc2hlZCB0aGUgaW5pdGlhbCB0YWJsZSBzeW5jaHJvbml6 YXRpb24KZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9yZWYvcHJlcGFyZV90cmFuc2FjdGlv bi5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9wcmVwYXJlX3RyYW5zYWN0aW9uLnNnbWwKaW5k ZXggZjRmNjExOGFjMzEuLjIyZjRmZWNhNjRkIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwv cmVmL3ByZXBhcmVfdHJhbnNhY3Rpb24uc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvcmVmL3By ZXBhcmVfdHJhbnNhY3Rpb24uc2dtbApAQCAtMTMyLDcgKzEzMiwxMCBAQCBQUkVQQVJFIFRS QU5TQUNUSU9OIDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1ldGVyIj50cmFuc2FjdGlvbl9p ZDwvcmVwbGFjZWFibGU+CiAgICAgY29udGludWVzIHRvIGhvbGQgd2hhdGV2ZXIgbG9ja3Mg aXQgaGVsZC4gIFRoZSBpbnRlbmRlZCB1c2FnZSBvZiB0aGUKICAgICBmZWF0dXJlIGlzIHRo YXQgYSBwcmVwYXJlZCB0cmFuc2FjdGlvbiB3aWxsIG5vcm1hbGx5IGJlIGNvbW1pdHRlZCBv cgogICAgIHJvbGxlZCBiYWNrIGFzIHNvb24gYXMgYW4gZXh0ZXJuYWwgdHJhbnNhY3Rpb24g bWFuYWdlciBoYXMgdmVyaWZpZWQgdGhhdAotICAgIG90aGVyIGRhdGFiYXNlcyBhcmUgYWxz byBwcmVwYXJlZCB0byBjb21taXQuCisgICAgb3RoZXIgZGF0YWJhc2VzIGFyZSBhbHNvIHBy ZXBhcmVkIHRvIGNvbW1pdC4gIEEgdHJhbnNhY3Rpb24gcHJlcGFyZWQgYnkgYQorICAgIGxv Z2ljYWwgcmVwbGljYXRpb24gYXBwbHkgd29ya2VyIGNhbiBiZSBjb21wbGV0ZWQgb25seSBi eSB0aGF0IHdvcmtlciwgc2VlCisgICAgPHhyZWYgbGlua2VuZD0ic3FsLWNyZWF0ZXN1YnNj cmlwdGlvbi1wYXJhbXMtd2l0aC10d28tcGhhc2UiLz4gZm9yIGEKKyAgICBsb2NrIHdhaXQg dGhpcyBjYW4gY2F1c2UuCiAgICA8L3BhcmE+CiAKICAgIDxwYXJhPgotLSAKMi41NC4wIChB cHBsZSBHaXQtMTU3KQoK --------------stf6Vwhq7gPDO03DhfSbKmdC--