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 1xSt8N-00000002DRV-0mFl for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Nov 2026 09:36:19 +0000 Received: from localhost ([127.0.0.1]:54344 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSt8M-0000000Hawo-0X14 for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Nov 2026 09:36:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:50826) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSt8L-0000000Hawe-3EaV for pgsql-hackers@lists.postgresql.org; Mon, 16 Nov 2026 09:36:17 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xSt8K-00000001BQ6-121E for pgsql-hackers@lists.postgresql.org; Mon, 16 Nov 2026 09:36:16 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-6b7245ab5e2so6731947a12.1 for ; Mon, 16 Nov 2026 01:36:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794821774; x=1795426574; 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=EvIpKim4Vmnr/KAXFTgOEc906+bLe/qdrD14wJXt83s=; b=jGkVE69a568OtitIFB8nvamFcDipnqVf7AnfvwTO/4BEt4ho137iv+SUkKqNMAd4Lq cwzuE/4RDg7ae74TIUJfm9IUUlCu2wrgPucQ4NCOlN/N3QsjMW1Yi3tJZmCSqAdNiPTk auVRTBWSvjq0OSDUycXrCX4Xi6bcpIHUSj+xTHafzdWcxcW70bWhVMCF6ohU/8V5gzNt iteryw0SHAb6Fw03X5yjuLJlGK4f0oag5heTlmwubmelfmjnkv1lXT9vXu4cDcUwpRW/ 0gSbPuKa7E+YHx7+Rrsih4/kbS1Bt5dSv6WV/d5wlfotJ3UBz/oArCHT6Mji4hLN741a 4f5Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794821774; x=1795426574; 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=EvIpKim4Vmnr/KAXFTgOEc906+bLe/qdrD14wJXt83s=; b=sPoV7YwexheeWBtLklyLfzvHfkmTv6lMw8L+ySqmaA3pcKEVoKxHzUznMxZooajYSj gU/zfSzHkAK1IM9TVfTwGNq3H7ZKdclCj8+hD5Q9TpQOygMAdVW0vT1YUqZvIW7155br UHMcbeqNJVTZM4SfVNfeSpjjQkrbxNUCTxYe7Ci+7Et4IvNSrlopjmTX7K8a433mW9kF sIeXIeuS52cBA5Bsrar9JpHCe2UeXZ4TP2BY+h6MVSPZJx0t2hN3sIEUN7pUjR1bQaH1 R91RlJf2XINhNfGMI2K+y+NBJ4vWEdjErhj0UtseMRNJ3wk7dRKttPczp8u2AyJLS7DV eE4A== X-Gm-Message-State: APuU4Z8WyMgUhSomAeDGEF2WyPvIZw1MXzu2BZPPggX2/S159JgNEj36 tCiXdHQpVUUSbjmKxIljaiCjdYn/BEc3z58ffnw/fq4EXeqY2XAqZcrm X-Gm-Gg: AYBFou3HeOn9ZFJLTQb/sRJxlSM0szjaNtMklXDQKE10umRGHgTMVqaf5hd9znric2H 2JPpzMoYvxiXDWQGQugURW4mB2Fgd9vyi8FdkBj6wZ/As+i8FtDF6QHsKPNCXUdLbFgiiNxrTC7 8s1d1IlRKor1lx1zUATXvO1INV6Y773Zg42wNpKml5WZ2olhf2PQsIdICve23gk4neEtiDspBzE Jdhjfn/6PStHZU/XNjWBJwTaqGYlHJkvGkdBUjtKhAg7g7+TsiyFvybRVHZqKgYTNp83sKZMpiI nEu52jazkOcqXmEFJ46+yBcDWguPq1hiNC6nKRourX72UAbCrJeqoD+v/BIinCqRzqzwTWL/c13 HmAmU1XOlJ21nPix1VKXP2hfntbJM2qiWQRCguPyOP5n67XuzV0KI9MoHPs2HoANNV8pq4Y9Tg+ KiHBtRvT1Tg3ntEnPmVEvYtNjMetn+xOZ+E1U031YkBeNB0Cj/WPNPHeQkTh6Le9gvezbHYsQFg 4vnKdaxa/QUPX9KF9LGRcO3p9/6zzEvBEt0dpn7mnsTn8BtGBr2neoR8pFBphzhaN8CSfAsXlEK ZWxNhoO5bjwFV4Gc65F08O/IuRnJaA3ryrmtZV/G01V5YMSfN1XBcfLod8p1JM8RwThXapWzZiR Fdi4asvcFxFjc X-Received: by 2002:a17:907:1b15:b0:c41:9a35:17a2 with SMTP id a640c23a62f3a-c507c4a4ee1mr1128873766b.31.1794821774330; Mon, 16 Nov 2026 01:36:14 -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-49c69406640sm20778017f8f.12.2026.11.16.01.36.13 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 16 Nov 2026 01:36:13 -0800 (PST) Date: Mon, 16 Nov 2026 09:36:12 +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> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, 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. === 1 I wonder if it would also be worth mentioning a nonzero lock_timeout as a possible mitigation too? === 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. > 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. Yeah, I agree. Teaching the lock manager about this logical dependency seems too complex. > 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. Agreed. FWIW, lock_timeout on the local session handles this multi-table case too. > 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? Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com