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 1xTDAh-00000000BgB-3wOs for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Nov 2026 07:00:04 +0000 Received: from localhost ([127.0.0.1]:52446 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xTDAh-00000003HPi-1500 for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Nov 2026 07:00:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:36192) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xTDAg-00000003HPa-47Ia for pgsql-hackers@lists.postgresql.org; Tue, 17 Nov 2026 07:00:02 +0000 Received: from mail-pj2-x0b.google.com ([2607:f8b0:4864:39::b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xTDAe-000000006c6-3vJQ for pgsql-hackers@lists.postgresql.org; Tue, 17 Nov 2026 07:00:02 +0000 Received: by mail-pj2-x0b.google.com with SMTP id 98e67ed59e1d1-3b3bbd46625so38771a91.1 for ; Mon, 16 Nov 2026 23:00:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794898800; x=1795503600; darn=lists.postgresql.org; h=content-transfer-encoding:content-type:in-reply-to:from:references :cc:to:subject:user-agent:mime-version:date:message-id:from:to:cc :subject:date:message-id:reply-to:content-type; bh=fo0/iVhUgoOVRim5kaJcRC8oJcX7IkIeeaYSz36ioX8=; b=F1p7vBVigaMgfOpvQyR8hgm/TlGW9YgvFEnAVJ8t5jvbMBdhQC4OA4EAvOcIE+U9Fs hSCe8P7PVtvXwqvHXHLnD/OWQu3FGJgqCq+rMlTYqN4FfK0aZ2fOZtuFWhxDBUpqMWn1 ODBikeAjQ+IKPbpPMrElazyYRbc1GN8RhOyXgn37AkW0/pPa8yaVSdIh6ALDbiHnlNSC OxJe/GKCXwlhJyPI6xoZTBbOyG6l9343B3xfrbwK+ig/njkDM5QdCirR9Pmvjo/PSip/ R7bso43EtgZwvBc/sC+7sveCRPFYH+re9sjbl3OCR0jfGC8iGQO8FIRK8l+GSnEtM02v wYgw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794898800; x=1795503600; h=content-transfer-encoding:content-type:in-reply-to:from:references :cc:to:subject:user-agent:mime-version:date:message-id:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=fo0/iVhUgoOVRim5kaJcRC8oJcX7IkIeeaYSz36ioX8=; b=LXtApKylpVmlEXfkFVWRWXhQlfRT4Eb60zZurdXauqPLDQ6F8DEH06vMz/UO9ZHFJq TQZOEYq1rAQPdIEWzW6UQOeDEB8kUE8fuimTQCPJe/enl5MStsT3uRvTX9nkDLrxD0NX Rc+h047dJ4qlLTnFGLfgz8dIVmohe3oVwferh+SmrjzFOecdljmVQW1u6VGRWeSugvkA vuO5DY1yb2xuB2fHeuAMDFtyGCR70ECnlWn0eauhN0eDXIGf6CwUUtakBkDcxYwYkW3S 0FS5pE4ohSeFiAt00+rUE8VdQx0fYEw6UMBqAkAALR1Ddrmdnzo8zapanKyPt1PwLmAN /Fbg== X-Forwarded-Encrypted: i=1; AL+hErHAVEkPI5OIl8IR0bpgkm5sebzyWzJXSWqve31+GMvNmwtEc123XMyh74ETms8hsVStKbiGG2xDkV/Eyeeu@lists.postgresql.org X-Gm-Message-State: APuU4Z/Rqg5VVvon980u5tvNX2zwZ4h/T1pvfRnD6HN0LQqNWfS3M/tn 4LDacuLGh1ZzfUfYBDkngCNJ6Q4eMhF/MdGaRjMGxtDhn1DItyvRXeYY X-Gm-Gg: AZFdTZe/hBx/bqd1iQxjheFz4Ayt2KpMRpxy0780ocGsAnEVt6A1sJ9CW5TmP1H2lGz JyXc+lTu2JCFzrO0V64/1y2ww3g+oV38x5uXR6QpDL6QVIO+GVcJfmoNcHLTeq/uUp+TL1nJZpT /Yqm9kQuwTNwvAtPHR0xHKaXah5VBzT3UjkXtAsxJkme3+3CULixUCVfwhSBJ0Dc+S0gjLAk6Rd 7oLYNeEkl3n0t494OoYXCU+ccusTMtkzI1wcKILvub9CcRee5ZcnfGlkLxzzcgnW1qeIMsea/hj /2Vc6avaOGJZHFpFsx88wLtJO8NMu083EWEBzUWrvgYJ6359rMV1e94uVPxv4O0y55r7EonqhJG 48cY5PuoIUliUJKhII9axzGb5y+4AGnSOaWU2ahylOqysR9wVUgaPBY+87rXFkYOrU1fvl9ENvm 3LN6AuARCTF87iGudTH9ZFWTDCGpHyS9qRrgTBO0q9OEHU4D1SNS+P28a5qBB084qJHSImcMXWc KN2oHBnBUkf3MTtWZuT+BfSAqJ1twQq2dmOQs4ARPfX4cVkyURvGc39o/O6v6Js9cd8qkmW5veW VEZwr0ypoQXyDfo0GzuxM+qaBNJ+Yac0OQOeRXoxGgJoJCToby1YC+dKyjq2EDRyHCm4yFp90uo gxTJxsoeVqjNdQMsI2qk0cYaxPUEYezEEVyXHpbID5Y3OGnqIln/s16wbYBZsOI20QE/ShNex5E jaGgFdj8MJSY5NM1GRnNVbLpzSXO1VL1XVhO/w X-Received: by 2002:a17:90b:584b:b0:3a7:9e3d:b3 with SMTP id 98e67ed59e1d1-3b1b0ccefabmr12013435a91.66.1794898800470; Mon, 16 Nov 2026 23:00:00 -0800 (PST) Received: from ?IPV6:2400:79c0:20d:180:982d:8d89:33f7:e984? ([2403:300:600d:d80:a4ea:8d89:33f7:e984]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-2efd7a11c4asm9173155ad.22.2026.11.16.22.59.57 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Nov 2026 23:00:00 -0800 (PST) Message-ID: Date: Tue, 17 Nov 2026 14:59:55 +0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Document prepared transaction lock waits with two_phase subscriptions To: shveta malik Cc: Bertrand Drouvot , "pgsql-hackers@lists.postgresql.org" , "amit.kapila16@gmail.com" , Nisha Moond , Masahiko Sawada , Dilip Kumar , "Hayato Kuroda (Fujitsu)" , shveta malik References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> From: Zhijie Hou In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On Tue, Nov17, 2026 at 02:26 PM shveta malik wrote: > I looked at v2 and have a few suggestions, which you can take if you agree. Thanks for the comments. > 1) > + This situation is not > + detected as a deadlock, so replication can remain stalled > + indefinitely. > > How about a little elaborate: > > Because the prepared transaction is not waiting on any lock itself, > this is not detected as a deadlock, and replication can remain stalled > indefinitely. I think if a transaction can be prepared, it naturally means it doesn't conflict with the others, so I'd keep the current version in the patch. > > 2) > > + indefinitely. To resolve this, either cancel the waiting session, > + or manually roll back the prepared transaction on the subscriber, > + disable two_phase (see > + linkend="sql-altersubscription-params-set">ALTER SUBSCRIPTION > ... SET) > + until that transaction is committed on the publisher and replicated > + again, and then re-enable two_phase if required. > + > > > --We can separate 2 suggestions, else it becomes difficult to > understand (at-least I find it difficult). Agreed. > --Also we can improve second suggestion a bit. > > How about: > > To recover, preferably cancel the waiting session, allowing the apply > worker to proceed and complete the prepared transaction normally. > Alternatively, execute ROLLBACK PREPARED on the subscriber to release > the locks. However, this discards the changes made by the prepared > transaction on the subscriber, so the transaction must be re-applied > once it is committed on the publisher, to keep the subscriber > consistent with the publisher. To achieve this, temporarily disable > two_phase using ALTER SUBSCRIPTION ... SET until the transaction is > committed on the publisher and replicated to the subscriber, then > re-enable it. The proposed text for the second point feels a bit verbose to me. I'd prefer to keep it short and clean. -- Best Regards, Zhijie Hou