pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Paquier <michael@paquier.xyz>
To: Zhijie Hou <houzhijie22@gmail.com>
Cc: shihao zhong <zhong950419@gmail.com>
Cc: Takeshi Ideriha <iderihatakeshi@gmail.com>
Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Cc: alpkeles99@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
Date: Fri, 23 Oct 2026 08:42:14 +0900
Message-ID: <atqfVnAaBcTPbqtK@paquier.xyz> (raw)
In-Reply-To: <CAFvd2n88xQWm-TJQWWa73A-Y04sG-0Qw9o_3eLtg7n4KEoSW6A@mail.gmail.com>
References: <5fff280b-96c4-497f-bbfc-0af95b9844b3@gmail.com>
	<CAGRkXqSo9vZ=7mw_X0tjH1-Wwdz8nWu9xpdX1x8hVM8tCazKAA@mail.gmail.com>
	<949ad7b3-2b0e-40dc-b6ac-e84da432149b@gmail.com>
	<atcbPTfcO9rihmbD@paquier.xyz>
	<atceNofaGIE2dOFn@paquier.xyz>
	<CAGRkXqStu17-_568HNTqz_Qh7=FKTaRO2A572hizQuhMBbExvQ@mail.gmail.com>
	<athl6okS1xEgasD3@paquier.xyz>
	<CAFvd2n_qVC1yqVEZXrh3W1gandgWUcYsOgsiY7xzU_Vy=qxWdQ@mail.gmail.com>
	<atlzUChbZatun_HQ@paquier.xyz>
	<CAFvd2n88xQWm-TJQWWa73A-Y04sG-0Qw9o_3eLtg7n4KEoSW6A@mail.gmail.com>

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

Attachments:

0001-Set-HEAP_XMAX_INVALID-for-tuples-updated-by-aborted-.patch (text/plain, 4.1K) download From 582b60ee6ec9bb7e48d252254bd2f868a0a76b9f Mon Sep 17 00:00:00 2001 From: Michael Paquier <michael@paquier.xyz> 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/access/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, Buffer buffer, TransactionId *de if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tuple))) 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/expected/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) +-- 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 = 0.0001; +SET LOCAL seq_page_cost = 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 = 2; +ROLLBACK TO sp; +CLUSTER cluster_aborted_upd USING cluster_aborted_upd_idx; +COMMIT; +-- One row expected. +SELECT count(*) FROM cluster_aborted_upd; + count +------- + 1 +(1 row) + +SELECT * FROM cluster_aborted_upd; + a | b +---+--- + 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/cluster.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 = n.relname WHERE o.relfilenode <> n.relfilenode ORDER BY o.relname; +-- 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 = 0.0001; +SET LOCAL seq_page_cost = 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 = 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; DROP USER regress_clstr_user; -- 2.56.0
signature.asc (application/pgp-signature, 832B) download

view thread (30+ messages)  latest in thread

Message-ID: <atqfVnAaBcTPbqtK@paquier.xyz>
Permalink:  ../atqfVnAaBcTPbqtK@paquier.xyz/
Also on:    postgresql.org/message-id/atqfVnAaBcTPbqtK@paquier.xyz

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: michael@paquier.xyz, houzhijie22@gmail.com, zhong950419@gmail.com, iderihatakeshi@gmail.com, ayushtiwari.slg01@gmail.com, alpkeles99@gmail.com
  Subject: Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  In-Reply-To: <atqfVnAaBcTPbqtK@paquier.xyz>
  References: <5fff280b-96c4-497f-bbfc-0af95b9844b3@gmail.com> <CAGRkXqSo9vZ=7mw_X0tjH1-Wwdz8nWu9xpdX1x8hVM8tCazKAA@mail.gmail.com> <949ad7b3-2b0e-40dc-b6ac-e84da432149b@gmail.com> <atcbPTfcO9rihmbD@paquier.xyz> <atceNofaGIE2dOFn@paquier.xyz> <CAGRkXqStu17-_568HNTqz_Qh7=FKTaRO2A572hizQuhMBbExvQ@mail.gmail.com> <athl6okS1xEgasD3@paquier.xyz> <CAFvd2n_qVC1yqVEZXrh3W1gandgWUcYsOgsiY7xzU_Vy=qxWdQ@mail.gmail.com> <atlzUChbZatun_HQ@paquier.xyz> <CAFvd2n88xQWm-TJQWWa73A-Y04sG-0Qw9o_3eLtg7n4KEoSW6A@mail.gmail.com> <atqfVnAaBcTPbqtK@paquier.xyz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox