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 1xK2QV-00000000ZJJ-11KD for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 23:42:28 +0000 Received: from localhost ([127.0.0.1]:54784 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xK2QT-0000000DUTk-28Ya for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 23:42:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:38722) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xK2QS-0000000DUTb-2Ekh for pgsql-bugs@lists.postgresql.org; Thu, 22 Oct 2026 23:42:25 +0000 Received: from fhigh-a1-smtp.messagingengine.com ([103.168.172.152]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xK2QQ-00000000Tku-14vG for pgsql-bugs@lists.postgresql.org; Thu, 22 Oct 2026 23:42:23 +0000 Received: from phl-compute-02.internal (phl-compute-02.internal [10.202.2.42]) by mailfhigh.phl.internal (Postfix) with ESMTP id 8261A1400078 for ; Thu, 22 Oct 2026 19:42:20 -0400 (EDT) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-02.internal (MEProxy); Thu, 22 Oct 2026 19:42:20 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1792712540; x=1792798940; bh=/3Ll69776F N63Qc6xlHuWiFeHUPUilcGMm2gtvMlRrg=; b=gesZRuDRLVMTdoCJMYGYE6sb4N beUEKu2EAmBRB+33uFJzVth8fPGKElwFAnMtOKTK5De9rrV7m72EY0MC0SAtitVD kMLwrhzBmy5Kldx7Pb8dVtEUYDEPfxqnS5lfDAz8RMUWQPvUS3wOXdMNRATLKuvd VqD3DworBEwFlaVxMkKGdWt4PrklP2fD85FWgCw8fx/sGmO3pOEgkK98OGTDZPAx 9kk0M/uNDW59cCN8bWupMF0PdICaIhKkLEk4pFMxrxV9155EWvDNJKFf9oPviseW fRw6VX59jioM8bHR2AYT83gmu6Bi/rdCskHG6rbfTDyqcgJdcFvpmzLusFnA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t= 1792712540; x=1792798940; bh=/3Ll69776FN63Qc6xlHuWiFeHUPUilcGMm2 gtvMlRrg=; b=T6bDwSMbPGf1DOsd50TLLdwDURJxltRUrOjsQN4y3PG3BbKG8Gx k3DdKi1ZKGb6a1RlnnJs1lZ5wTEAT9Z+GsgNqXYDUfxW0Hs6tzcubLi/yTl0P8WI 71fHbIDiCIpt/MnY0o3vjDz2OXE5vcNtEAw1G88XrfX2DLjMl1d7WU13+3iewHCu YP8JLzn8nvhnEqthEeZVv430GYBQFSBBprhPhAdpjJySYn6/z/o+S6uvNOaNVrBV /06YbsAKjGM+6Uq/MpB8ebN94lsi0021s1hDwe1jQ3EMcjnD/dC7Zd3eoXSEtk6c BfqB3kIxsn31BsftOZFuuANpR+MLOcZislQ== X-DKIM2-Info: draft=ietf-dkim-dkim2-spec-07; repo=github.com/dkim2wg/interop; date=2026-10-18; sw=lmtpprox; action=sign d=paquier.xyz a=rsa-sha256; DKIM2-Signature: i=1; m=1; t=1792712540; d=paquier.xyz; mf=PG1pY2hhZWxAcGFxdWllci54eXo+; rt=PHBnc3FsLWJ1Z3NAbGlzdHMucG9zdGdyZXNxbC5vcmc+; s=fm2:rsa-sha256:DS9ffuHZdDfbndaBWOndNfCvuSTQxkm61uQ2Zhd3zQznoKl anXbQJcD6CaJubLoYIYj2Tl1Qm/jFFEIMnsEDnJ+hqHtU3tGUy9JlxcxUMUSicwz oGc5OJ4qva/Y6NjM7mF6/8yTx8PcrScyx60kJmbOki4g2uN+YWr/TxfJLE+npXoC BvhSvf7c7eWs7ZSe2+VtigY6LXztgiinTjoT9YoHjRv6dMwGwmzaEvFsGO762e42 liCkrHyTKD8mloZqlt7c+vP+/XGCp1SoIigLIkIZdOE3U3Th7owVJ98hhl5vZwvO b5EVADC160LZRzUeqRs6C2pcT2gEiEXoIs8Jt1A==; X-DKIM2-Info: draft=ietf-dkim-dkim2-spec-07; repo=github.com/dkim2wg/interop; date=2026-10-18; sw=lmtpprox; action=mi-m=1; hc=12; hn=cc,content-disposition,content-type,date,feedback-id,from, in-reply-to,message-id,mime-version,references,subject,to; Message-Instance: m=1; h=sha256:iZygcON0kps5oePEB4XCQeSftGVL6KhR+c0l4rsmCEQ=:TnefcDAgpaltsQSea0Bh8y1I00Czz6PiOa8ZrS1rD80=; X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: dmFkZTF853iKGxhoPLrUujbjM3b6sNLk1TE7zoksu2gwzJ0sG9G+csYgAd1de3MflytY39 wJL+BrEJJUxQoTFPpnrxWTyxoclC05E6JXjcY42uhEw43awHZgcQ35bDmHESjpWphzMden LBeSTb4ujp38uXtRvvRqQDny8YXfyTohrZk2xXP5nJeA80+7mFhYP1c3uGfxvhabWcfCGt rCkzWsivR5B3wUjOPNHxT7iE45G0ptYtIk80A/byyJZ0wUohwBiHUE9og13xiFU4dZiGpG fmiN7il4TTcgT+Eac0D7co1Z3zVd6UB0nGl0fSHsB53zmP3z7+AcR4z+hpiBtbpWlEe2m4 G9x4r1gOBE1tZUtoNAtjOQZMBuYu00Wp9ONhlbjOcq8FX7kO4g0FfLfm5YpdVNkG4lmtPa YBrubnJDTb/oknM8lapKhJwmgZQNLOIrQC9AglUNnrULRZjoSE8g3xnenhhoWScgTBNviW eDBlIuGV1xlj4Dd4Ht3xmOx/KDypJb7yQLi2I2isK0Hrj5I1RsHbeZWqzxMyLFnoTjg7lh CS6Ljy3JBQAwtm0xpiwMkQ8cJBvgWcBcgjup0O0NlAETcuvWkSKh2IB0LYjutz8no2idSw 76q/KLnV3SEHwwSfmdlzCMKwWJqgTxt97zRwyH2NhP0qycvBvRSBgXp7Hr1g X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 22 Oct 2026 19:42:18 -0400 (EDT) Date: Fri, 23 Oct 2026 08:42:14 +0900 From: Michael Paquier To: Zhijie Hou Cc: shihao zhong , Takeshi Ideriha , Ayush Tiwari , alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Message-ID: References: <5fff280b-96c4-497f-bbfc-0af95b9844b3@gmail.com> <949ad7b3-2b0e-40dc-b6ac-e84da432149b@gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="6iJtyaQ4tyDcRlOP" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --6iJtyaQ4tyDcRlOP Content-Type: multipart/mixed; boundary="2c2W9OHQiXdjOfb7" Content-Disposition: inline --2c2W9OHQiXdjOfb7 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Thu, Oct 22, 2026 at 04:53:41PM +0800, Zhijie Hou wrote: > +1 to fix that part first. That part of the fix (patch) looks clean to me. It > also covers more common cases, which can happen for both user tables and > catalogs, while the other part (the complicated, performance-sensitive one) only > covers catalog cases IIUC, as user table DML holds locks until transaction end, > so concurrent CLUSTER can't touch it in that case. Please find attached a patch to do this part, for now. > One idea to improve is to do these expensive checks only when xmin is not equal > to xmax. If xmin is still in progress, xmax cannot be aborted, so it's safe to > return early in that case. The DELETE test wouldn't regress after this change, > since xmin and xmax differ there. The subxact_update test would still regress, > but that's likely a rare case. Yeah, the xmin <> xmax check would make sense here. I'd like to suspect that you should be able to make back for most of the performance regression of v2 with tweaks like that. How does the attached look to you? -- Michael --2c2W9OHQiXdjOfb7 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=0001-Set-HEAP_XMAX_INVALID-for-tuples-updated-by-aborted-.patch Content-Transfer-Encoding: quoted-printable =46rom 582b60ee6ec9bb7e48d252254bd2f868a0a76b9f Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Fri, 23 Oct 2026 08:41:39 +0900 Subject: [PATCH] Set HEAP_XMAX_INVALID for tuples updated by aborted subtransaction HeapTupleSatisfiesVacuumHorizon() did not set HEAP_XMAX_INVALID on a tuple inserted, then updated by a subtransaction that rolled back. rewriteheap.c relies on this hint to tell if such tuples should be treated as updated and would drop them, causing rewrites to lose some tuples. HeapTupleSatisfiesVacuumHorizon() is updated to set the hint HEAP_XMAX_INVALID, so as heapam is able to redirect the tuple to its correct route, and does not lose it. This issue impacted both CLUSTER (down to v14) and REPACK [USING] (down to v19). Discussion: https://postgr.es/m/ Backpatch-through: 14 --- src/backend/access/heap/heapam_visibility.c | 2 ++ src/test/regress/expected/cluster.out | 28 +++++++++++++++++++++ src/test/regress/sql/cluster.sql | 19 ++++++++++++++ 3 files changed, 49 insertions(+) diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/acce= ss/heap/heapam_visibility.c index 361b76e50652..905444ae0360 100644 --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -1178,6 +1178,8 @@ HeapTupleSatisfiesVacuumHorizon(HeapTuple htup, Buffe= r buffer, TransactionId *de if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tup= le))) return HEAPTUPLE_DELETE_IN_PROGRESS; /* deleting subtransaction must have aborted */ + SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, + InvalidTransactionId); return HEAPTUPLE_INSERT_IN_PROGRESS; } else if (TransactionIdIsInProgress(HeapTupleHeaderGetRawXmin(tuple))) diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expec= ted/cluster.out index d99c90cced59..280bb8d2d23f 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -880,6 +880,33 @@ ORDER BY o.relname; clstr_3 (2 rows) =20 +-- CLUSTER with rollbacked subtransaction, where the same row is inserted, +-- updated, then rollbacked. +BEGIN; +-- Index scan required for CLUSTER, force it. +SET LOCAL random_page_cost =3D 0.0001; +SET LOCAL seq_page_cost =3D 1000; +CREATE TABLE cluster_aborted_upd (a int, b int); +CREATE INDEX cluster_aborted_upd_idx ON cluster_aborted_upd (b); +INSERT INTO cluster_aborted_upd VALUES (1, 3); +SAVEPOINT sp; +UPDATE cluster_aborted_upd SET a =3D 2; +ROLLBACK TO sp; +CLUSTER cluster_aborted_upd USING cluster_aborted_upd_idx; +COMMIT; +-- One row expected. +SELECT count(*) FROM cluster_aborted_upd; + count=20 +------- + 1 +(1 row) + +SELECT * FROM cluster_aborted_upd; + a | b=20 +---+--- + 1 | 3 +(1 row) + -- clean up DROP TABLE clustertest; DROP TABLE clstr_1; @@ -888,4 +915,5 @@ DROP TABLE clstr_3; DROP TABLE clstr_4; DROP TABLE clstr_expression; DROP TABLE clstrpart; +DROP TABLE cluster_aborted_upd; DROP USER regress_clstr_user; diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluste= r.sql index eb0da1cf22e9..b636f93aa547 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -454,6 +454,24 @@ JOIN relnodes_new n ON o.relname =3D n.relname WHERE o.relfilenode <> n.relfilenode ORDER BY o.relname; =20 +-- CLUSTER with rollbacked subtransaction, where the same row is inserted, +-- updated, then rollbacked. +BEGIN; +-- Index scan required for CLUSTER, force it. +SET LOCAL random_page_cost =3D 0.0001; +SET LOCAL seq_page_cost =3D 1000; +CREATE TABLE cluster_aborted_upd (a int, b int); +CREATE INDEX cluster_aborted_upd_idx ON cluster_aborted_upd (b); +INSERT INTO cluster_aborted_upd VALUES (1, 3); +SAVEPOINT sp; +UPDATE cluster_aborted_upd SET a =3D 2; +ROLLBACK TO sp; +CLUSTER cluster_aborted_upd USING cluster_aborted_upd_idx; +COMMIT; +-- One row expected. +SELECT count(*) FROM cluster_aborted_upd; +SELECT * FROM cluster_aborted_upd; + -- clean up DROP TABLE clustertest; DROP TABLE clstr_1; @@ -462,5 +480,6 @@ DROP TABLE clstr_3; DROP TABLE clstr_4; DROP TABLE clstr_expression; DROP TABLE clstrpart; +DROP TABLE cluster_aborted_upd; =20 DROP USER regress_clstr_user; --=20 2.56.0 --2c2W9OHQiXdjOfb7-- --6iJtyaQ4tyDcRlOP Content-Type: application/pgp-signature; name=signature.asc -----BEGIN PGP SIGNATURE----- iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmran1YACgkQnvQgOdby QH058g/+PdhE5FHhtjet+n5jwGWZezfkWurqjP/Xth6NjjPjoTE7ephyhx51AWH3 TqTXwTfkMMLQurbMN2Bfk0i46BygXo6IDjPMvPLVtbvQV7pu9lFUr3/hvby66EDE EiromNOvP7knW/WfFtenrecZZ27lNZLcfXltY9UzhwUZgBFvJEry2DgaX8MAc6ue Lj6HXZ0PfNUbbNklgKlZaMGbYyzLsI1J/HZcYLYYmydSF04UFiOv2hHMAuTSm6Az kbCmUzJZXUe7KzDwO0XQ4Lh9LFcFmHH+fem8wsyva9yIhju91ZOYdiAbAY0F8IT2 hnUYRFhg4qwHB9hvnqJo8wjY9gr2sLPScFiOJ5btXlac2rV84uNBnw7frmX32DFY IK7iYyaIvzLkTCSZkQGjLvFVvW78KT6pHMqmz35XE7Np0Zed911mbFwHNa8Obm3k 3+V92JaF/hEOW6DEwv6jXFl8d6MZgVSKUArGAP5ulO9IX+06in18RWOiX9DEERAP V4nSjHBnPEcz3moKPiSa4ITHHpDsjCqzlx+kxeuqHm4lU/UgVprkRxUItXmRFtVD ENuKk/Aey583MFRbmJuR+zET3lWcJJXsYo6Ov4FLzSToUW0zi27ZC/SVbHqT2WEf tHcUzDjO3wvtM43uSsUNOOWbwhsBI3Bg8jNCKImYhylbQ3c+/58= =vllO -----END PGP SIGNATURE----- --6iJtyaQ4tyDcRlOP--