pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
30+ messages / 7 participants
[nested] [flat]

Commits from this thread:
  2026-10-26 e7f4463e86 928c2eeed5 5df84b355a 08f0cec8fb 07d1b44181 0b56567494 278d2a672e  Set HEAP_XMAX_INVALID for tuples updated by aborted subtransaction
* BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
@ 2026-10-15 13:59 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: PG Bug reporting form @ 2026-10-15 13:59 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com

The following bug has been logged on the website:

Bug reference:      19760
Logged by:          Alperen Keles
Email address:      alpkeles99@gmail.com
PostgreSQL version: 18.6
Operating system:   macOS 26 arm64 (also seen on Ubuntu 24.04 x86_64)
Description:        

CLUSTER (and REPACK on master) silently drops a live row when the same
transaction inserted the row and then updated it inside a subtransaction
that was rolled back. No error is raised; the row is gone after COMMIT.

  CREATE TABLE t1(c0 int, c1 int);
  CREATE INDEX t1_i0 ON t1 (c1);
  BEGIN;
  INSERT INTO t1 VALUES (1, 1);
  SAVEPOINT s1; UPDATE t1 SET c0 = 2; ROLLBACK TO s1;
  CLUSTER t1 USING t1_i0;
  SELECT count(*) FROM t1;   -- 0, expected 1
  COMMIT;
  SELECT count(*) FROM t1;   -- 0, expected 1

Reproduced with default settings on 18.6 (REL_18_STABLE a85b5523e67),
14.24 and master 158b6d5109 (all --enable-cassert --enable-debug, macOS
arm64); also found on master on Ubuntu x86_64. Rechecked on 2026-10-15
(Ubuntu 24.04, x86_64, gcc 13.3, --enable-cassert --enable-debug, server
under ulimit -v 6 GB):
the same on 19rc1 (REL_19_STABLE 0fb192f1b02), master aa7b9cf132b and
REL_18_STABLE 4d62ef92fda. CLUSTER VERBOSE reports a
negative "dead row versions cannot be removed yet" count (-1 here).

Found with a query fuzzer.








^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-16 02:55 ` Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Takeshi Ideriha @ 2026-10-16 02:55 UTC (permalink / raw)
  To: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi

Thank you for reporting this.

> 
> CLUSTER (and REPACK on master) silently drops a live row when the same
> transaction inserted the row and then updated it inside a subtransaction
> that was rolled back. No error is raised; the row is gone after COMMIT.
> 
>   CREATE TABLE t1(c0 int, c1 int);
>   CREATE INDEX t1_i0 ON t1 (c1);
>   BEGIN;
>   INSERT INTO t1 VALUES (1, 1);
>   SAVEPOINT s1; UPDATE t1 SET c0 = 2; ROLLBACK TO s1;
>   CLUSTER t1 USING t1_i0;
>   SELECT count(*) FROM t1;   -- 0, expected 1
>   COMMIT;
>   SELECT count(*) FROM t1;   -- 0, expected 1
> 
While I was reproducing the bug, I noticed that if we SELECT rows before
CLUSTER, the issue won't happen. This suggests that not setting hint bit
is very likely to be related to the issue.

----
BEGIN;
INSERT INTO t1 VALUES (1, 1);
SAVEPOINT s1;
UPDATE t1 SET c0 = 2;
ROLLBACK TO s1;

SELECT count(*) FROM t1; -- SELECT before CLUSTER

CLUSTER VERBOSE t1 USING t1_i0;
SELECT count(*) FROM t1;
---

-- RESULT --
 count
-------
     1
(1 row)

INFO:  repacking "public.t1" using index scan on "t1_i0"
INFO:  "public.t1": found 0 removable, 1 nonremovable row versions in 1
pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
CLUSTER
 count
-------
     1
(1 row)

COMMIT
 count
-------
     1
(1 row)


> REL_18_STABLE 4d62ef92fda. CLUSTER VERBOSE reports a
> negative "dead row versions cannot be removed yet" count (-1 here).

The value is tups_recently_dead in heapam_relation_copy_for_cluster().
tups_recently_dead decreases only when rewrite_heap_dead_tuple()
returnes TRUE in rewriteheap.c. I guess there is something wrong with
visibility check invloving hint bits in rewriteheap.c. I'll look into
this further.

Regards,
Takeshi Ideriha






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
@ 2026-10-16 09:09   ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Ayush Tiwari @ 2026-10-16 09:09 UTC (permalink / raw)
  To: Takeshi Ideriha <iderihatakeshi@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Fri, 16 Oct 2026 at 08:25, Takeshi Ideriha <iderihatakeshi@gmail.com> wrote:
>
> Hi
>
> Thank you for reporting this.
>
> >
> > CLUSTER (and REPACK on master) silently drops a live row when the same
> > transaction inserted the row and then updated it inside a subtransaction
> > that was rolled back. No error is raised; the row is gone after COMMIT.
> >
> >   CREATE TABLE t1(c0 int, c1 int);
> >   CREATE INDEX t1_i0 ON t1 (c1);
> >   BEGIN;
> >   INSERT INTO t1 VALUES (1, 1);
> >   SAVEPOINT s1; UPDATE t1 SET c0 = 2; ROLLBACK TO s1;
> >   CLUSTER t1 USING t1_i0;
> >   SELECT count(*) FROM t1;   -- 0, expected 1
> >   COMMIT;
> >   SELECT count(*) FROM t1;   -- 0, expected 1
> >
> While I was reproducing the bug, I noticed that if we SELECT rows before
> CLUSTER, the issue won't happen. This suggests that not setting hint bit
> is very likely to be related to the issue.
>
> ----
> BEGIN;
> INSERT INTO t1 VALUES (1, 1);
> SAVEPOINT s1;
> UPDATE t1 SET c0 = 2;
> ROLLBACK TO s1;
>
> SELECT count(*) FROM t1; -- SELECT before CLUSTER
>
> CLUSTER VERBOSE t1 USING t1_i0;
> SELECT count(*) FROM t1;
> ---
>
> -- RESULT --
>  count
> -------
>      1
> (1 row)
>
> INFO:  repacking "public.t1" using index scan on "t1_i0"
> INFO:  "public.t1": found 0 removable, 1 nonremovable row versions in 1
> pages
> DETAIL:  0 dead row versions cannot be removed yet.
> CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
> CLUSTER
>  count
> -------
>      1
> (1 row)
>
> COMMIT
>  count
> -------
>      1
> (1 row)
>
>
> > REL_18_STABLE 4d62ef92fda. CLUSTER VERBOSE reports a
> > negative "dead row versions cannot be removed yet" count (-1 here).
>
> The value is tups_recently_dead in heapam_relation_copy_for_cluster().
> tups_recently_dead decreases only when rewrite_heap_dead_tuple()
> returnes TRUE in rewriteheap.c. I guess there is something wrong with
> visibility check invloving hint bits in rewriteheap.c. I'll look into
> this further.

I think you are right. It looks like the missing HEAP_XMAX_INVALID
hint in HeapTupleSatisfiesVacuumHorizon() lets the rewrite defer copying
the live original tuple and then discard it when it encounters the aborted
successor.

Regards,
Ayush






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
@ 2026-10-19 03:31     ` shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: shihao zhong @ 2026-10-19 03:31 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: Takeshi Ideriha <iderihatakeshi@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

Here is a patch. rewrite_heap_dead_tuple() drops the held tuple when its
successor is dead, but that is only right if the successor's inserter
committed. If it aborted, the update never happened, so 0001 keeps the
tuple and adds its logical rewrite mapping.

Setting the hint in HeapTupleSatisfiesVacuum() is not enough. VACUUM
FULL on a catalog can see a transaction that is still open, and this
loses the pg_class row of a committed table:

  s1: BEGIN; CREATE TABLE t (a int);
  s1: SAVEPOINT sp; ALTER TABLE t ADD COLUMN b int; ROLLBACK TO sp;
  s2: VACUUM FULL pg_class;
  s1: COMMIT;

0002 is an isolation test for that case, it is optional. But I want it in
tree to prevent
future regression in that area.

Thanks,
Shihao

Attachments: download all (2 files, .tar.gz)

v1-0001-Don-t-drop-a-live-tuple-when-its-successor-s-inse.patch (application/octet-stream, 2.2K) download From b3a0fea0c7499d1db40754b77770f4d3cb7793e2 Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Sun, 18 Oct 2026 22:50:32 -0400 Subject: [PATCH v1 1/2] Don't drop a live tuple when its successor's inserter aborted When CLUSTER saw the dead successor of a tuple it was holding back, rewrite_heap_dead_tuple() discarded that tuple too. If the successor's inserter aborted, the update never happened and the tuple is live. Keep it in that case. Discussion: https://postgr.es/m/19760-8f9a0eba17bc8c92@postgresql.org --- src/backend/access/heap/rewriteheap.c | 14 ++++++++++++-- 1 file changed, 12 insertions(+), 2 deletions(-) diff --git a/src/backend/access/heap/rewriteheap.c b/src/backend/access/heap/rewriteheap.c index 0648e433031..a9cc21717fa 100644 --- a/src/backend/access/heap/rewriteheap.c +++ b/src/backend/access/heap/rewriteheap.c @@ -301,8 +301,8 @@ end_heap_rewrite(RewriteState state) UnresolvedTup unresolved; /* - * Write any remaining tuples in the UnresolvedTups table. If we have any - * left, they should in fact be dead, but let's err on the safe side. + * Write any remaining tuples in the UnresolvedTups table. Usually they + * are dead, but they are live if the update aborted. */ hash_seq_init(&seq_status, state->rs_unresolved_tups); @@ -310,6 +310,8 @@ end_heap_rewrite(RewriteState state) { ItemPointerSetInvalid(&unresolved->tuple->t_data->t_ctid); raw_heap_insert(state, unresolved->tuple); + logical_rewrite_heap_tuple(state, unresolved->old_tid, + unresolved->tuple); } /* Write the last page, if any */ @@ -574,6 +576,14 @@ rewrite_heap_dead_tuple(RewriteState state, HeapTuple old_tuple) if (unresolved != NULL) { + /* + * If the inserter of this tuple aborted, the update never happened + * and the earlier tuple is not dead. It will be written at the end. + */ + if (!HeapTupleHeaderXminCommitted(old_tuple->t_data) && + !TransactionIdDidCommit(HeapTupleHeaderGetXmin(old_tuple->t_data))) + return false; + /* Need to free the contained tuple as well as the hashtable entry */ heap_freetuple(unresolved->tuple); hash_search(state->rs_unresolved_tups, &hashkey, -- 2.37.1 (Apple Git-137.1)
v1-0002-Add-a-test-for-CLUSTER-with-an-aborted-update.patch (application/octet-stream, 3.1K) download From 1b3639e0dd7e15a47d6893cdeb495faa382e6234 Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Sun, 18 Oct 2026 22:50:32 -0400 Subject: [PATCH v1 2/2] Add a test for CLUSTER with an aborted update An isolation test for a pg_class row whose update was rolled back while its inserting transaction was still open. VACUUM FULL pg_class used to lose the row. Discussion: https://postgr.es/m/19760-8f9a0eba17bc8c92@postgresql.org --- .../cluster-catalog-aborted-update.out | 13 +++++++++++ src/test/isolation/isolation_schedule | 1 + .../specs/cluster-catalog-aborted-update.spec | 22 +++++++++++++++++++ 3 files changed, 36 insertions(+) create mode 100644 src/test/isolation/expected/cluster-catalog-aborted-update.out create mode 100644 src/test/isolation/specs/cluster-catalog-aborted-update.spec diff --git a/src/test/isolation/expected/cluster-catalog-aborted-update.out b/src/test/isolation/expected/cluster-catalog-aborted-update.out new file mode 100644 index 00000000000..43feaebc5e7 --- /dev/null +++ b/src/test/isolation/expected/cluster-catalog-aborted-update.out @@ -0,0 +1,13 @@ +Parsed test spec with 2 sessions + +starting permutation: s1_create s1_alter s2_vacfull s1_commit s2_check +step s1_create: BEGIN; CREATE TABLE cluster_catalog_tab (a int); +step s1_alter: SAVEPOINT sp; ALTER TABLE cluster_catalog_tab ADD COLUMN b int; ROLLBACK TO sp; +step s2_vacfull: VACUUM FULL pg_class; +step s1_commit: COMMIT; +step s2_check: SELECT count(*) FROM pg_class WHERE relname = 'cluster_catalog_tab'; +count +----- + 1 +(1 row) + diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index bf9a2037c70..d47b59040c4 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -122,6 +122,7 @@ test: plpgsql-toast test: cluster-conflict test: cluster-conflict-partition test: cluster-toast-value-reuse +test: cluster-catalog-aborted-update test: truncate-conflict test: serializable-parallel test: serializable-parallel-2 diff --git a/src/test/isolation/specs/cluster-catalog-aborted-update.spec b/src/test/isolation/specs/cluster-catalog-aborted-update.spec new file mode 100644 index 00000000000..e2b792fa3f2 --- /dev/null +++ b/src/test/isolation/specs/cluster-catalog-aborted-update.spec @@ -0,0 +1,22 @@ +# VACUUM FULL on a catalog, with an aborted update by a concurrent +# transaction +# +# s1 creates a table and then updates its pg_class row in a subtransaction +# that rolls back. s1 is still open when s2 rewrites pg_class, and no one +# has set HEAP_XMAX_INVALID on the pg_class row yet. The row must survive. + +teardown +{ + DROP TABLE IF EXISTS cluster_catalog_tab; +} + +session s1 +step s1_create { BEGIN; CREATE TABLE cluster_catalog_tab (a int); } +step s1_alter { SAVEPOINT sp; ALTER TABLE cluster_catalog_tab ADD COLUMN b int; ROLLBACK TO sp; } +step s1_commit { COMMIT; } + +session s2 +step s2_vacfull { VACUUM FULL pg_class; } +step s2_check { SELECT count(*) FROM pg_class WHERE relname = 'cluster_catalog_tab'; } + +permutation s1_create s1_alter s2_vacfull s1_commit s2_check -- 2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
@ 2026-10-19 05:15       ` Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Takeshi Ideriha @ 2026-10-19 05:15 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Shihao, Ayush

On 2026/10/19 12:31, shihao zhong wrote:
> Hi,
> 
> Here is a patch. rewrite_heap_dead_tuple() drops the held tuple when its
> successor is dead, but that is only right if the successor's inserter
> committed. If it aborted, the update never happened, so 0001 keeps the
> tuple and adds its logical rewrite mapping.

> Setting the hint in HeapTupleSatisfiesVacuum() is not enough. VACUUM
> FULL on a catalog can see a transaction that is still open, and this
> loses the pg_class row of a committed table:
I was actually also writing a code to fix the issue and have just
noticed the patch was already submitted. Attached a patch for reference.
I haven't considered into logical_rewrite_heap_tuple(). I need to check
this... Thank you.

> 	{
>+		/*
>+		 * If the inserter of this tuple aborted, the update >never happened
>+		 * and the earlier tuple is not dead.  It will be >written at the end.
>+		 */
>+		if (!HeapTupleHeaderXminCommitted(old_tuple->t_data) &&
>+			!>TransactionIdDidCommit(HeapTupleHeaderGetXmin(old_tuple->t_data)))
>+			return false;

I am thinking like the below since HeapTupleSatisfiesVacuum set the xmin
invalid hint bit for the dead tuple. So I believe we can just rely on
HeapTupleHeaderXminInvalid hint bit.

+               if (HeapTupleHeaderXminInvalid(old_tuple->t_data))
+                       return false;

What do you think above?

>0002 is an isolation test for that case, it is optional. But I want it
>in tree to prevent
>future regression in that area.

+1. I also think this is optional but I believe the issue is easy to
oversight, so adding a new test would be helpful.

Regards,
Takeshi Ideriha

Attachments:

v1-0001-fix-cluster-misses-a-live-tuple-updated-by-subtra.patch (text/plain, 2.3K) download From 4a2b03449fa148c944e3b682cef622b2bfa3aee5 Mon Sep 17 00:00:00 2001 From: Takeshi Ideriha <iderihatakeshi@gmail.com> Date: Mon, 19 Oct 2026 13:58:40 +0900 Subject: [PATCH v2] fix cluster misses a live tuple updated by subtrasanction which will be aborted --- src/backend/access/heap/rewriteheap.c | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) diff --git a/src/backend/access/heap/rewriteheap.c b/src/backend/access/heap/rewriteheap.c index 0648e433031..df9c17606a0 100644 --- a/src/backend/access/heap/rewriteheap.c +++ b/src/backend/access/heap/rewriteheap.c @@ -554,6 +554,15 @@ rewrite_heap_dead_tuple(RewriteState state, HeapTuple old_tuple) * when xmin of a tuple is greater than xmax, which sounds * counter-intuitive but is perfectly valid. * + * There is one exception. Suppose a tuple is created by a transaction, and + * then updated in a subtransaction, and that subtransaction is aborted. + * The explanation in the paragraph above assumes that the dead tuple was + * once committed. In this case, the dead tuple was never committed, so the + * earlier tuple is not nesessarily dead. Also, in this case, the earlier + * tuple is put in the unresolved table because no hint bit is assigned to + * this tuple and HeapTupleSatisfiesVacuum judges it as INSERT_IN_PROGRESS. + * As a result, a tuple that is actually live would not be copied. + * * We don't bother to try to detect the situation the other way round, * when we encounter the dead tuple first and then the recently dead one * that points to it. If that happens, we'll have some unmatched entries @@ -574,6 +583,17 @@ rewrite_heap_dead_tuple(RewriteState state, HeapTuple old_tuple) if (unresolved != NULL) { + /* + * In this case, the earlier tuple is not necessarily dead. We should + * copy this tuple to a new table. It's fine to leave this tuple in + * unresolved table since end_heap_rewrite will copy the tuples remained + * in the table with invalid ctid. + * + * We can rely on hint bit since HeapTupleSatisfiesVacuum set the hint + * bit for the dead tuple. + */ + if (HeapTupleHeaderXminInvalid(old_tuple->t_data)) + return false; /* Need to free the contained tuple as well as the hashtable entry */ heap_freetuple(unresolved->tuple); hash_search(state->rs_unresolved_tups, &hashkey, -- 2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
@ 2026-10-20 02:22         ` shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: shihao zhong @ 2026-10-20 02:22 UTC (permalink / raw)
  To: Takeshi Ideriha <iderihatakeshi@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Takeshi,

> So I believe we can just rely on HeapTupleHeaderXminInvalid hint bit.

I tried it and it keeps the row in both cases. But it also needs the
logical_rewrite_heap_tuple() call in end_heap_rewrite(). Without it,
logical decoding after VACUUM FULL pg_class fails:

  ERROR:  could not map filenumber "base/16490/16491" to relation OID

Thanks,
Shihao

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
@ 2026-10-20 02:32           ` Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: Takeshi Ideriha @ 2026-10-20 02:32 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Shihao


>> So I believe we can just rely on HeapTupleHeaderXminInvalid hint bit.
> 
> I tried it and it keeps the row in both cases. But it also needs the
> logical_rewrite_heap_tuple() call in end_heap_rewrite(). Without it,
> logical decoding after VACUUM FULL pg_class fails:
> 
>   ERROR:  could not map filenumber "base/16490/16491" to relation OID
> 
Thank you for replying. Glad to know it worked for both two cases.

Regarding logical_rewrite_heap_tuple() part, you are completely right. I
tested by myself yesterday and hit the same error while decoding the
vacuum full scenario. Thank you for pointing out. I was not aware of DDL
case with logical replication.

Regards,
Takeshi Ideriha







^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
@ 2026-10-20 07:41             ` Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-10-20 07:41 UTC (permalink / raw)
  To: Takeshi Ideriha <iderihatakeshi@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Tue, Oct 20, 2026 at 11:32:44AM +0900, Takeshi Ideriha wrote:
> Regarding logical_rewrite_heap_tuple() part, you are completely right. I
> tested by myself yesterday and hit the same error while decoding the
> vacuum full scenario. Thank you for pointing out. I was not aware of DDL
> case with logical replication.

@@ -574,6 +576,14 @@ rewrite_heap_dead_tuple(RewriteState state, HeapTuple old_tuple)
 
 	if (unresolved != NULL)
 	{
+		/*
+		 * If the inserter of this tuple aborted, the update never happened
+		 * and the earlier tuple is not dead.  It will be written at the end.
+		 */
+		if (!HeapTupleHeaderXminCommitted(old_tuple->t_data) &&
+			!TransactionIdDidCommit(HeapTupleHeaderGetXmin(old_tuple->t_data)))
+			return false;

Having a recheck of the old tuple's state within
rewrite_heap_dead_tuple() feels very incorrect to me, based on the
bunch of comments at the top of rewriteheap.c.  It sounds to me that
rewrite_heap_dead_tuple() should be called when we know that the tuple
is dead, no?  Isn't heapam_relation_copy_for_cluster() the bit actually
incorrect here, marking the tuple with "isdead" to start with?  Your
solution of a recheck to make sure that an old tuple is actually dead
in a code path where an old tuple is assumed to be absolutely and
definitely dead looks like an API contract violation regarding
rewriteheap.c.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-20 07:54               ` Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-10-20 07:54 UTC (permalink / raw)
  To: Takeshi Ideriha <iderihatakeshi@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Tue, Oct 20, 2026 at 04:41:49PM +0900, Michael Paquier wrote:
> Having a recheck of the old tuple's state within
> rewrite_heap_dead_tuple() feels very incorrect to me, based on the
> bunch of comments at the top of rewriteheap.c.  It sounds to me that
> rewrite_heap_dead_tuple() should be called when we know that the tuple
> is dead, no?  Isn't heapam_relation_copy_for_cluster() the bit actually
> incorrect here, marking the tuple with "isdead" to start with?  Your
> solution of a recheck to make sure that an old tuple is actually dead
> in a code path where an old tuple is assumed to be absolutely and
> definitely dead looks like an API contract violation regarding
> rewriteheap.c.

Looking a bit more at that before the end of the day, I think that the
suggestion of touching HeapTupleSatisfiesVacuumHorizon() and setting
some hint bits would be the correct thing to do.  One case that matters
is the "deleting subtransaction must have aborted" case.  I am pretty
sure that we should also worry about the case where we check for
TransactionIdIsInProgress(HeapTupleHeaderGetRawXmin(tuple)), which
looks reachable, and set an extra hint bit there..
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-21 01:20                 ` shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: shihao zhong @ 2026-10-21 01:20 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Michael,

>> Isn't heapam_relation_copy_for_cluster() the bit actually
>> incorrect here, marking the tuple with "isdead" to start with?

Thanks for looking at this so closely. You are right that the recheck
broke the contract of rewriteheap.c.

> Looking a bit more at that before the end of the day, I think that
> the suggestion of touching HeapTupleSatisfiesVacuumHorizon() and
> setting some hint bits would be the correct thing to do.

Agreed, and thanks for pointing at both places. v2 does just that and
leaves rewriteheap.c alone.

With the hint set, the old version no longer waits in unresolved_tups,
so the logical mapping change from v1 is not needed anymore.

Thanks,
Shihao

Attachments: download all (2 files, .tar.gz)

v2-0002-Add-a-test-for-CLUSTER-with-an-aborted-update.patch (application/octet-stream, 3.9K) download From 3aaf9784ea62232dccbac6bcb7f41e4b76c29cca Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Tue, 20 Oct 2026 20:02:49 -0400 Subject: [PATCH v2 2/2] Add a test for CLUSTER with an aborted update An isolation test for a pg_class row whose update was rolled back while its inserting transaction was still open. VACUUM FULL pg_class used to lose the row. Discussion: https://postgr.es/m/19760-8f9a0eba17bc8c92@postgresql.org --- .../cluster-catalog-aborted-update.out | 22 ++++++++++++++++ src/test/isolation/isolation_schedule | 1 + .../specs/cluster-catalog-aborted-update.spec | 26 +++++++++++++++++++ 3 files changed, 49 insertions(+) create mode 100644 src/test/isolation/expected/cluster-catalog-aborted-update.out create mode 100644 src/test/isolation/specs/cluster-catalog-aborted-update.spec diff --git a/src/test/isolation/expected/cluster-catalog-aborted-update.out b/src/test/isolation/expected/cluster-catalog-aborted-update.out new file mode 100644 index 00000000000..399fb8728a4 --- /dev/null +++ b/src/test/isolation/expected/cluster-catalog-aborted-update.out @@ -0,0 +1,22 @@ +Parsed test spec with 2 sessions + +starting permutation: s1_create s1_alter s2_vacfull s1_commit s2_check +step s1_create: BEGIN; CREATE TABLE cluster_catalog_tab (a int); +step s1_alter: SAVEPOINT sp; ALTER TABLE cluster_catalog_tab ADD COLUMN b int; ROLLBACK TO sp; +step s2_vacfull: VACUUM FULL pg_class; +step s1_commit: COMMIT; +step s2_check: SELECT count(*) FROM pg_class WHERE relname = 'cluster_catalog_tab'; +count +----- + 1 +(1 row) + + +starting permutation: s1_plain s1_count +step s1_plain: BEGIN; CREATE TABLE cluster_plain_tab (a int, b int); CREATE INDEX cluster_plain_idx ON cluster_plain_tab (b); INSERT INTO cluster_plain_tab VALUES (1, 1); SAVEPOINT sp; UPDATE cluster_plain_tab SET a = 2; ROLLBACK TO sp; CLUSTER cluster_plain_tab USING cluster_plain_idx; COMMIT; +step s1_count: SELECT count(*) FROM cluster_plain_tab; +count +----- + 1 +(1 row) + diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index bf9a2037c70..d47b59040c4 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -122,6 +122,7 @@ test: plpgsql-toast test: cluster-conflict test: cluster-conflict-partition test: cluster-toast-value-reuse +test: cluster-catalog-aborted-update test: truncate-conflict test: serializable-parallel test: serializable-parallel-2 diff --git a/src/test/isolation/specs/cluster-catalog-aborted-update.spec b/src/test/isolation/specs/cluster-catalog-aborted-update.spec new file mode 100644 index 00000000000..b76c37d064c --- /dev/null +++ b/src/test/isolation/specs/cluster-catalog-aborted-update.spec @@ -0,0 +1,26 @@ +# VACUUM FULL on a catalog, with an aborted update by a concurrent +# transaction +# +# s1 creates a table and then updates its pg_class row in a subtransaction +# that rolls back. s1 is still open when s2 rewrites pg_class, and no one +# has set HEAP_XMAX_INVALID on the pg_class row yet. The row must survive. + +teardown +{ + DROP TABLE IF EXISTS cluster_catalog_tab; + DROP TABLE IF EXISTS cluster_plain_tab; +} + +session s1 +step s1_create { BEGIN; CREATE TABLE cluster_catalog_tab (a int); } +step s1_alter { SAVEPOINT sp; ALTER TABLE cluster_catalog_tab ADD COLUMN b int; ROLLBACK TO sp; } +step s1_commit { COMMIT; } +step s1_plain { BEGIN; CREATE TABLE cluster_plain_tab (a int, b int); CREATE INDEX cluster_plain_idx ON cluster_plain_tab (b); INSERT INTO cluster_plain_tab VALUES (1, 1); SAVEPOINT sp; UPDATE cluster_plain_tab SET a = 2; ROLLBACK TO sp; CLUSTER cluster_plain_tab USING cluster_plain_idx; COMMIT; } +step s1_count { SELECT count(*) FROM cluster_plain_tab; } + +session s2 +step s2_vacfull { VACUUM FULL pg_class; } +step s2_check { SELECT count(*) FROM pg_class WHERE relname = 'cluster_catalog_tab'; } + +permutation s1_create s1_alter s2_vacfull s1_commit s2_check +permutation s1_plain s1_count -- 2.37.1 (Apple Git-137.1)
v2-0001-Set-HEAP_XMAX_INVALID-for-aborted-updates-of-in-p.patch (application/octet-stream, 2.2K) download From 5ab4599c9d99d06ce14b38ac6f7cb25fc8285893 Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Tue, 20 Oct 2026 20:02:49 -0400 Subject: [PATCH v2 1/2] Set HEAP_XMAX_INVALID for aborted updates of in-progress tuples HeapTupleSatisfiesVacuumHorizon() left the hint unset when the inserting transaction was still open and the updating subtransaction had rolled back. rewriteheap.c treated such a tuple as updated and dropped it when it saw the dead new version, so CLUSTER and VACUUM FULL lost the row. Discussion: https://postgr.es/m/19760-8f9a0eba17bc8c92@postgresql.org --- src/backend/access/heap/heapam_visibility.c | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/access/heap/heapam_visibility.c index 361b76e5065..7184adfe221 100644 --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -1178,10 +1178,30 @@ 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))) { + /* + * If the updater of the tuple has already aborted (a rolled-back + * subtransaction of the inserter), remember that, as callers like + * rewriteheap.c rely on HEAP_XMAX_INVALID to tell a live tuple + * from an updated one. + */ + if (!(tuple->t_infomask & HEAP_XMAX_INVALID) && + !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) && + !HeapTupleHeaderIsOnlyLocked(tuple)) + { + TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple); + + if (!TransactionIdIsInProgress(xmax) && + !TransactionIdDidCommit(xmax)) + SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, + InvalidTransactionId); + } + /* * It'd be possible to discern between INSERT/DELETE in progress * here by looking at xmax - but that doesn't seem beneficial for -- 2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
@ 2026-10-21 07:12                   ` Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-10-21 07:12 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; +Cc: Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Tue, Oct 20, 2026 at 09:20:18PM -0400, shihao zhong wrote:
> Agreed, and thanks for pointing at both places. v2 does just that and
> leaves rewriteheap.c alone.
> 
> With the hint set, the old version no longer waits in unresolved_tups,
> so the logical mapping change from v1 is not needed anymore.

I can see that you are testing both code paths, at least.  The
coverage part looks good, but I'd suggest to make the test more
readable at least especially in the s1_alter and s1_plain that include
a bunch of queries.  There two are hard to parse.

+                if (!TransactionIdIsInProgress(xmax) &&
+                    !TransactionIdDidCommit(xmax))
+                    SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
+                                InvalidTransactionId)

It's also worth noting that these may make the code less efficient,
with more SLRU traffic.  What could be the class of worst case
possible?  A combination of an in-progress transaction with some
VACUUM activity while the in-progress transaction is still opened?
VACUUM would need to do more work with the patch in place, where I
guess it could be slower if we have a bunch of tuples that need to
have this hint bit set.

Or perhaps this is too narrow to worry about?  Anyway, we should be
super careful with such changes.  That can bite hard.

If others have any ideas and/or comments, please feel free..

Thanks,
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-21 10:01                     ` Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: Zhijie Hou @ 2026-10-21 10:01 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Wed, Oct 21, 2026 at 3:13 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, Oct 20, 2026 at 09:20:18PM -0400, shihao zhong wrote:
> > Agreed, and thanks for pointing at both places. v2 does just that and
> > leaves rewriteheap.c alone.
> >
> > With the hint set, the old version no longer waits in unresolved_tups,
> > so the logical mapping change from v1 is not needed anymore.
>
> I can see that you are testing both code paths, at least.  The
> coverage part looks good, but I'd suggest to make the test more
> readable at least especially in the s1_alter and s1_plain that include
> a bunch of queries.  There two are hard to parse.
>
> +                if (!TransactionIdIsInProgress(xmax) &&
> +                    !TransactionIdDidCommit(xmax))
> +                    SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
> +                                InvalidTransactionId)
>
> It's also worth noting that these may make the code less efficient,
> with more SLRU traffic.  What could be the class of worst case
> possible?  A combination of an in-progress transaction with some
> VACUUM activity while the in-progress transaction is still opened?
> VACUUM would need to do more work with the patch in place, where I
> guess it could be slower if we have a bunch of tuples that need to
> have this hint bit set.
>
> Or perhaps this is too narrow to worry about?  Anyway, we should be
> super careful with such changes.  That can bite hard.
>
> If others have any ideas and/or comments, please feel free..

I haven't thought of a better idea yet, but I noticed a potential race condition
for MultiXacts in the current fix:

+ if (!(tuple->t_infomask & HEAP_XMAX_INVALID) &&
+ !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) &&
+ !HeapTupleHeaderIsOnlyLocked(tuple))
+ {
+ TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple);
+
+ if (!TransactionIdIsInProgress(xmax) &&
+ !TransactionIdDidCommit(xmax))
+ SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
+ InvalidTransactionId);

If a tuple's Xmax is a MultiXact and the updating subtransaction is still in
progress when HeapTupleHeaderIsOnlyLocked() is called, it returns false.
However, if that updating subtransaction aborts before the second
TransactionIdIsInProgress() check, the second check returns false and sets
HEAP_XMAX_INVALID on the tuple even though the MultiXact still has active
members, which I think violates the MultiXact hint-bit standard.

This can cause a problem if another transaction locked the tuple (e.g., FOR KEY
SHARE) beforehand. Once HEAP_XMAX_INVALID is set on the tuple, another backend
can lock the tuple FOR UPDATE even though the FOR KEY SHARE lock has not been
released.

I can reproduce this if a transaction updates a tuple, a second session locks
it FOR KEY SHARE, the first transaction updates it again in a subtransaction,
and a concurrent backend runs a SELECT that triggers on-access pruning
(heap_prune_satisfies_vacuum() -> HeapTupleSatisfiesVacuumHorizon()).

See the attached patch (which applies on top of the v2 patches and adds an
injection point) to reproduce this.

Best Regards,
Zhijie Hou

Attachments:

nocfbot-0001-Top-up-for-v2-0001-Avoid-setting-HEAP_XMAX_INVAL.patch (application/octet-stream, 6.0K) download From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Wed, 21 Oct 2026 16:40:00 +0800 Subject: [PATCH] Top-up for v2-0001: Reproduce HEAP_XMAX_INVALID clobbering MultiXact lock. Add an injection point and isolation test permutation on top of v2-0001 to show that if an updating subtransaction in a MultiXact aborts after !HeapTupleHeaderIsOnlyLocked(tuple) returns true, v2-0001 sets HEAP_XMAX_INVALID on the MultiXact tuple and clobbers an active FOR KEY SHARE lock held by another transaction. --- src/backend/access/heap/heapam_visibility.c | 3 + .../expected/multixact-aborted-updater.out | 36 ++++++++++++ .../specs/multixact-aborted-updater.spec | 58 ++++++++++++++++++- 3 files changed, 96 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/access/heap/heapam_visibility.c index 7184adfe221..6e47109d781 100644 --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -76,6 +76,7 @@ #include "storage/bufmgr.h" #include "storage/procarray.h" #include "utils/builtins.h" +#include "utils/injection_point.h" #include "utils/snapmgr.h" @@ -1195,6 +1196,8 @@ HeapTupleSatisfiesVacuumHorizon(HeapTuple htup, Buffer buffer, TransactionId *de { TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple); + INJECTION_POINT("heap-vacuum-horizon-inprogress-xmin", NULL); + if (!TransactionIdIsInProgress(xmax) && !TransactionIdDidCommit(xmax)) SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, diff --git a/src/test/modules/injection_points/expected/multixact-aborted-updater.out b/src/test/modules/injection_points/expected/multixact-aborted-updater.out index 40750714734..14a6d85dc21 100644 --- a/src/test/modules/injection_points/expected/multixact-aborted-updater.out +++ b/src/test/modules/injection_points/expected/multixact-aborted-updater.out @@ -82,3 +82,39 @@ id|filler 2|initial (1 row) + +starting permutation: s1begin s1update s2begin s2lock s1subupd s3attach s3htsv s1rollbacksp wake_htsv s1commit s3forupdnowait s2commit +step s1begin: BEGIN; +step s1update: UPDATE mxact_abort SET filler = 's1' WHERE id = 1; +step s2begin: BEGIN; +step s2lock: SELECT * FROM mxact_abort WHERE id = 1 FOR KEY SHARE; +id|filler +--+------- + 1|initial +(1 row) + +step s1subupd: + SELECT FROM injection_points_detach('transaction-abort-after-clog'); + SAVEPOINT sp; + UPDATE mxact_abort SET filler = repeat('x', 800) WHERE id = 1; + +step s3attach: + SELECT FROM injection_points_set_local(); + SELECT FROM injection_points_attach('heap-vacuum-horizon-inprogress-xmin', 'wait'); + +step s3htsv: SELECT * FROM mxact_abort; <waiting ...> +step s1rollbacksp: ROLLBACK TO sp; +step wake_htsv: + SELECT FROM injection_points_detach('heap-vacuum-horizon-inprogress-xmin'); + SELECT FROM injection_points_wakeup('heap-vacuum-horizon-inprogress-xmin'); + +step s1commit: COMMIT; +step s3htsv: <... completed> +id|filler +--+------- + 1|initial +(1 row) + +step s3forupdnowait: SELECT * FROM mxact_abort WHERE id = 1 FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "mxact_abort" +step s2commit: COMMIT; diff --git a/src/test/modules/injection_points/specs/multixact-aborted-updater.spec b/src/test/modules/injection_points/specs/multixact-aborted-updater.spec index 98d265a2e94..0eee3aa0611 100644 --- a/src/test/modules/injection_points/specs/multixact-aborted-updater.spec +++ b/src/test/modules/injection_points/specs/multixact-aborted-updater.spec @@ -19,7 +19,7 @@ setup { CREATE EXTENSION injection_points; - CREATE TABLE mxact_abort (id int PRIMARY KEY, filler text); + CREATE TABLE mxact_abort (id int PRIMARY KEY, filler text) WITH (fillfactor = 10); INSERT INTO mxact_abort VALUES (1, 'initial'); } @@ -38,26 +38,45 @@ setup { } step s1begin { BEGIN; } step s1update { UPDATE mxact_abort SET filler = 's1' WHERE id = 1; } +step s1subupd { + SELECT FROM injection_points_detach('transaction-abort-after-clog'); + SAVEPOINT sp; + UPDATE mxact_abort SET filler = repeat('x', 800) WHERE id = 1; +} +step s1rollbacksp { ROLLBACK TO sp; } +step s1commit { COMMIT; } step s1abort { ROLLBACK; } # Transaction 2 locks the row in KEY SHARE mode, to cause a multixact to be # created. session s2 +step s2begin { BEGIN; } step s2lock { SELECT * FROM mxact_abort WHERE id = 1 FOR KEY SHARE; } +step s2commit { COMMIT; } # Transaction 3 performs a concurrent operation while transaction 1 is aborting session s3 step s3nowait { SELECT * FROM mxact_abort WHERE id = 1 FOR NO KEY UPDATE NOWAIT; } +step s3forupdnowait { SELECT * FROM mxact_abort WHERE id = 1 FOR UPDATE NOWAIT; } step s3update { UPDATE mxact_abort SET filler = 's3' WHERE id = 1; } step s3delete { DELETE FROM mxact_abort WHERE id = 1; } step s3keyupdate { UPDATE mxact_abort SET id = 2 WHERE id = 1; } step s3check { SELECT * FROM mxact_abort ORDER BY id; } +step s3attach { + SELECT FROM injection_points_set_local(); + SELECT FROM injection_points_attach('heap-vacuum-horizon-inprogress-xmin', 'wait'); +} +step s3htsv { SELECT * FROM mxact_abort; } session waker step wake { SELECT FROM injection_points_detach('transaction-abort-after-clog'); SELECT FROM injection_points_wakeup('transaction-abort-after-clog'); } +step wake_htsv { + SELECT FROM injection_points_detach('heap-vacuum-horizon-inprogress-xmin'); + SELECT FROM injection_points_wakeup('heap-vacuum-horizon-inprogress-xmin'); +} # A SELECT .. NOWAIT while the updating transaction is paused in the abort # considers the updater as still running, and reports an error. @@ -105,3 +124,20 @@ permutation s3keyupdate wake s3check + +# HeapTupleSatisfiesVacuumHorizon() must not set HEAP_XMAX_INVALID on a tuple +# with a MultiXact Xmax when an updating subtransaction aborts while another +# transaction still holds a lock in the MultiXact. +permutation + s1begin + s1update + s2begin + s2lock + s1subupd + s3attach + s3htsv + s1rollbacksp + wake_htsv + s1commit + s3forupdnowait + s2commit -- 2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
@ 2026-10-22 02:22                       ` Michael Paquier <michael@paquier.xyz>
  2026-10-22 02:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-22 07:46                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 20:44                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  0 siblings, 4 replies; 30+ messages in thread

From: Michael Paquier @ 2026-10-22 02:22 UTC (permalink / raw)
  To: Zhijie Hou <houzhijie22@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Wed, Oct 21, 2026 at 06:01:28PM +0800, Zhijie Hou wrote:
> + if (!(tuple->t_infomask & HEAP_XMAX_INVALID) &&
> + !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) &&
> + !HeapTupleHeaderIsOnlyLocked(tuple))
> + {
> + TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple);
> +
> + if (!TransactionIdIsInProgress(xmax) &&
> + !TransactionIdDidCommit(xmax))
> + SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
> + InvalidTransactionId);
> 
> If a tuple's Xmax is a MultiXact and the updating subtransaction is still in
> progress when HeapTupleHeaderIsOnlyLocked() is called, it returns false.
> However, if that updating subtransaction aborts before the second
> TransactionIdIsInProgress() check, the second check returns false and sets
> HEAP_XMAX_INVALID on the tuple even though the MultiXact still has active
> members, which I think violates the MultiXact hint-bit standard.

Perhaps also check for HEAP_XMAX_IS_MULTI to skip the multixacts and
make sure that active members are not lost, then?  I am not completely
sure if this would be always right, though, these code paths are the
hard-level definition of tricky.

What I am really wondering is why do we actually need to fix *two*
problems at once?  The first SetHintBits() looks like a no-brainer to
me, and links to the second permutation in the test "s1_plain
s1_count" where we do a CLUSTER after an UPDATE rollbacked to a
previous savepoint.  I don't think that there is a need for an
isolation test on this one, either, wouldn't a plain SQL sequence be
sufficient?

That's not to mention that the first one can be hit with DDLs that do
some update on catalogs, as well.  Say, an ANALYZE with a CLUSTER
(this SQL sequence is a courtesy of Claude, I've just pointed to it
the possibility and the agent has confirmed the sequence):
BEGIN;
-- force the index-scan path
SET LOCAL random_page_cost = 0.0001;
SET LOCAL seq_page_cost = 1000;
CREATE TABLE s (a int);
INSERT INTO s VALUES (1);
ANALYZE s;
SAVEPOINT sp;
INSERT INTO s SELECT g FROM generate_series(2, 1000) g;
-- updates pg_statistic row
ANALYZE s;
ROLLBACK TO sp;
RELEASE sp;
CLUSTER VERBOSE pg_statistic USING pg_statistic_relid_att_inh_index;
COMMIT;
-- one row expected, none on HEAD.
SELECT count(*) FROM pg_statistic WHERE starelid = 's'::regclass;

The VACUUM FULL on pg_class is less exciting, points to the second
SetHintBits().  And as far as I can see, this one is actually the
source of a measurable regression.  To prove my point, see the SQL
scripts attached.  Use them like that:
1) Run setup.sql, for some relations.
2) Run holders.sql through a bunch of sessions (64 here, could be
more).  This is important to stress TransactionIdIsInProgress():
for i in $(seq 64); do
  psql -X -f holders.sql > /dev/null 2>&1 &
done
3) In a new session, run either subxact_updates.sql, delete.sql or
insert_only.sql.
3) Run vacuum.sql, measure its runtime.

Then with 500k rows, here are some VACUUM average runtimes in ms from
one of my laptops:
                HEAD  Shihao's v2
subxact_updates 1408         2123
delete           686         1369
insert_only      689          693

The subxact_updates is an edge case, fine, still it regresses badly.
The DELETE case is a much more common case.  It regresses even more
badly, and that does not look acceptable to me.  That should be
optimizable, of course, I don't really know yet how to do that.  I
know however that regressions like these ones are never good.

So the first step to me would be a "Divide and Conquer" strategy,
where we should look at each problem independently instead of trying
to do too much at once.
--
Michael

Attachments:

  setup.sql (application/sql, 152B) download

  delete.sql (application/sql, 193B) download

  insert_only.sql (application/sql, 145B) download

  subxact_updates.sql (application/sql, 412B) download

  vacuum.sql (application/sql, 173B) download

  holders.sql (application/sql, 346B) download

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-22 02:53                         ` Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-22 03:13                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  3 siblings, 1 reply; 30+ messages in thread

From: Takeshi Ideriha @ 2026-10-22 02:53 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; Zhijie Hou <houzhijie22@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi

> 
> What I am really wondering is why do we actually need to fix *two*
> problems at once?  The first SetHintBits() looks like a no-brainer to
> me, and links to the second permutation in the test "s1_plain
> s1_count" where we do a CLUSTER after an UPDATE rollbacked to a
> previous savepoint.  I don't think that there is a need for an
> isolation test on this one, either, wouldn't a plain SQL sequence be
> sufficient?
>

> So the first step to me would be a "Divide and Conquer" strategy,
> where we should look at each problem independently instead of trying
> to do too much at once.
Thank you for sorting the issue out and setting the direction. I was
thinking of leaving first SetHintBits() in
HeapTupleSatisfiesVacuumHorizon() and moving the second SetHintBits() to
heapam_relation_copy_for_cluster() to solve the performance issue.
However, it would still be another API violation, wouldn't it? We also
needs to figure out the multixact race issue. I'm trying to look into
multixact part as much as possible.

Regards,
Takeshi Ideriha






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 02:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
@ 2026-10-22 03:13                           ` Michael Paquier <michael@paquier.xyz>
  2026-10-22 04:55                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-10-22 03:13 UTC (permalink / raw)
  To: Takeshi Ideriha <iderihatakeshi@gmail.com>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Thu, Oct 22, 2026 at 11:53:29AM +0900, Takeshi Ideriha wrote:
> Thank you for sorting the issue out and setting the direction. I was
> thinking of leaving first SetHintBits() in
> HeapTupleSatisfiesVacuumHorizon() and moving the second SetHintBits() to
> heapam_relation_copy_for_cluster() to solve the performance issue.

Yeah, I don't think that we should begin using SetHintBits() outside
of heapam_visibility.c when it comes down to heap as a table AM.

> However, it would still be another API violation, wouldn't it? We also
> needs to figure out the multixact race issue. I'm trying to look into
> multixact part as much as possible.

What do you think about the first SetHintBits() added for the
subtransaction aborted case?  This one seems acceptable here, by
itself..
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 02:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-22 03:13                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-22 04:55                             ` Takeshi Ideriha <iderihatakeshi@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Takeshi Ideriha @ 2026-10-22 04:55 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org


On 2026/10/22 12:13, Michael Paquier wrote:
> Yeah, I don't think that we should begin using SetHintBits() outside> of
heapam_visibility.c when it comes down to heap as a table AM.

Sure, thank you for the reply.

>> However, it would still be another API violation, wouldn't it? We also
>> needs to figure out the multixact race issue. I'm trying to look into
>> multixact part as much as possible.
> 
> What do you think about the first SetHintBits() added for the
> subtransaction aborted case?  This one seems acceptable here, by
> itself..
+1 for this. Setting the hint bit for the aborted subtransaction inside
HeapTupleSatisfiesVacuumHorizon is safe and not harmful for the
performance.

Regards,
Takeshi Ideriha






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-22 07:46                         ` Takeshi Ideriha <iderihatakeshi@gmail.com>
  3 siblings, 0 replies; 30+ messages in thread

From: Takeshi Ideriha @ 2026-10-22 07:46 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; Zhijie Hou <houzhijie22@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi

On 2026/10/22 11:22, Michael Paquier wrote:
> On Wed, Oct 21, 2026 at 06:01:28PM +0800, Zhijie Hou wrote:
>> + if (!(tuple->t_infomask & HEAP_XMAX_INVALID) &&
>> + !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) &&
>> + !HeapTupleHeaderIsOnlyLocked(tuple))
>> + {
>> + TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple);
>> +
>> + if (!TransactionIdIsInProgress(xmax) &&
>> + !TransactionIdDidCommit(xmax))
>> + SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
>> + InvalidTransactionId);
>>
>> If a tuple's Xmax is a MultiXact and the updating subtransaction is still in
>> progress when HeapTupleHeaderIsOnlyLocked() is called, it returns false.
>> However, if that updating subtransaction aborts before the second
>> TransactionIdIsInProgress() check, the second check returns false and sets
>> HEAP_XMAX_INVALID on the tuple even though the MultiXact still has active
>> members, which I think violates the MultiXact hint-bit standard.
> 
> Perhaps also check for HEAP_XMAX_IS_MULTI to skip the multixacts and
> make sure that active members are not lost, then?  I am not completely
> sure if this would be always right, though, these code paths are the
> hard-level definition of tricky.
> 
I'm +1 for fixing the subtransaction issue first but let me add a
comment regarding this multixact race issue.

It should be OK not to set the hint bit for a tuple whose xmax is
MultiXact containing a key locker and an aborted updating
subtransaction. This is because rewrite_heap_tuple filters out this
tuple by HeapTupleHeaderIsOnlyLocked and doesn't put it into unresolved
table. I'm not sure if there is another issue we need to consider in
addition to the performance issue.

Regards,
Takeshi Ideriha






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-22 08:53                         ` Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  3 siblings, 1 reply; 30+ messages in thread

From: Zhijie Hou @ 2026-10-22 08:53 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Thu, Oct 22, 2026 at 10:22 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Wed, Oct 21, 2026 at 06:01:28PM +0800, Zhijie Hou wrote:
> > + if (!(tuple->t_infomask & HEAP_XMAX_INVALID) &&
> > + !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) &&
> > + !HeapTupleHeaderIsOnlyLocked(tuple))
> > + {
> > + TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple);
> > +
> > + if (!TransactionIdIsInProgress(xmax) &&
> > + !TransactionIdDidCommit(xmax))
> > + SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
> > + InvalidTransactionId);
> >
> > If a tuple's Xmax is a MultiXact and the updating subtransaction is still in
> > progress when HeapTupleHeaderIsOnlyLocked() is called, it returns false.
> > However, if that updating subtransaction aborts before the second
> > TransactionIdIsInProgress() check, the second check returns false and sets
> > HEAP_XMAX_INVALID on the tuple even though the MultiXact still has active
> > members, which I think violates the MultiXact hint-bit standard.
>
> Perhaps also check for HEAP_XMAX_IS_MULTI to skip the multixacts and
> make sure that active members are not lost, then?  I am not completely
> sure if this would be always right, though, these code paths are the
> hard-level definition of tricky.

Right, I think we can skip setting flag for HEAP_XMAX_IS_MULTI tuples,
since they're
handled correctly later in rewrite_heap_tuple() by HeapTupleHeaderIsOnlyLocked()
and won't be added to unresolved_tups. (I see Takeshi San also mentioned that).

>
> What I am really wondering is why do we actually need to fix *two*
> problems at once?  The first SetHintBits() looks like a no-brainer to
> me, and links to the second permutation in the test "s1_plain
> s1_count" where we do a CLUSTER after an UPDATE rollbacked to a
> previous savepoint.

+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.

> Then with 500k rows, here are some VACUUM average runtimes in ms from
> one of my laptops:
>                 HEAD  Shihao's v2
> subxact_updates 1408         2123
> delete           686         1369
> insert_only      689          693
>
> The subxact_updates is an edge case, fine, still it regresses badly.
> The DELETE case is a much more common case.  It regresses even more
> badly, and that does not look acceptable to me.  That should be
> optimizable, of course, I don't really know yet how to do that.  I
> know however that regressions like these ones are never good.

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.

>
> So the first step to me would be a "Divide and Conquer" strategy,
> where we should look at each problem independently instead of trying
> to do too much at once.

Agreed.

Best Regards,
Zhijie Hou






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
@ 2026-10-22 23:42                           ` Michael Paquier <michael@paquier.xyz>
  2026-10-23 01:45                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-23 05:59                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-24 12:57                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  0 siblings, 3 replies; 30+ messages in thread

From: Michael Paquier @ 2026-10-22 23:42 UTC (permalink / raw)
  To: Zhijie Hou <houzhijie22@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

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 ^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-23 01:45                             ` shihao zhong <zhong950419@gmail.com>
  2 siblings, 0 replies; 30+ messages in thread

From: shihao zhong @ 2026-10-23 01:45 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

Sorry for the slow reply. This turned out a bit more complex than I
expected, and I was busy with some personal stuff yesterday.

On Fri, Oct 23, 2026 at 08:42:14AM +0900, Michael Paquier wrote:
> How does the attached look to you?

Looks good to me. It fixes the report and your pg_statistic case,
and the test fails without the fix.

I think Manu's change for the second spot should go in too, then
both cases in this thread are fixed. I have an isolation test for
this case if that helps.

Thanks,
Shihao

Attachments:

  cluster-aborted-subxact-update.spec (application/octet-stream, 877B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-23 05:59                             ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2 siblings, 0 replies; 30+ messages in thread

From: Ayush Tiwari @ 2026-10-23 05:59 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Fri, 23 Oct 2026 at 05:12, Michael Paquier <michael@paquier.xyz> wrote:
>
> 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.

v1 looks good to me.

Regards,
Ayush






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-24 12:57                             ` Zhijie Hou <houzhijie22@gmail.com>
  2026-10-26 00:57                               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2 siblings, 1 reply; 30+ messages in thread

From: Zhijie Hou @ 2026-10-24 12:57 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Fri, Oct 23, 2026 at 7:42 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> 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?

Thanks for updating the patch, it looks good to me.

Best Regards,
Zhijie Hou






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-24 12:57                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
@ 2026-10-26 00:57                               ` Michael Paquier <michael@paquier.xyz>
  2026-10-26 15:41                                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-10-26 00:57 UTC (permalink / raw)
  To: Zhijie Hou <houzhijie22@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Sat, Oct 24, 2026 at 08:57:23PM +0800, Zhijie Hou wrote:
> Thanks for updating the patch, it looks good to me.

Thanks for the reviews.  Applied and backpatched this part down to
v14.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-24 12:57                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-26 00:57                               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-26 15:41                                 ` Manu <manuelreyesbravo@gmail.com>
  2026-10-26 23:34                                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: Manu @ 2026-10-26 15:41 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Michael,

On Mon, Oct 26, 2026 at 09:57:20AM +0900, Michael Paquier wrote:
> Thanks for the reviews.  Applied and backpatched this part down to
> v14.

Thanks for taking care of it.  The attached series adds the second
spot on top of e7f4463e869, as shihao asked upthread.

0001 sets HEAP_XMAX_INVALID in the branch of
HeapTupleSatisfiesVacuumHorizon() where the inserter is another
backend that is still running, once the updater has aborted.  It
skips xmax == xmin and MultiXacts, and it asks the clog first and
then the proc array, so it does not open the window Zhijie found in
my clog-only version.  It includes shihao's isolation spec, which
loses the pg_class row without the fix.  0002 is Zhijie's test for
that window.

VACUUM with your scripts, 64 holders, one run of 12 interleaved
rounds on 2026-10-25, ratios to HEAD:

  delete.sql: 1.00x
  subxact_updates.sql: 1.00x (with v2's check: 1.51x)
  aborted updates, first VACUUM: 1.07x, same as v2's check
  aborted updates, following VACUUMs: 1.00x

"Aborted updates" is a new script where each subtransaction rolls
back after its UPDATE.  It is the only case where 0001 asks the proc
array, once per tuple, in the VACUUM that sets the hint.  The
measured heapam_visibility.c matches 0001 except for two lines of a
test overlay.  Method and the script are in measurements.txt.

0001 and 0002 apply with git am on master and REL_19_STABLE.  On 14
to 18 the code applies as is, but the isolation_schedule line needs
another neighbor.  0002 only goes down to 17, where the injection
points it uses exist.  I ran make check and the isolation and
injection_points suites on master and 19, and make check and
isolation on 14.

The xmin != xmax check and the race are Zhijie's, the MultiXact skip
is yours, and the spec is shihao's.

Regards,
Manu

Attachments: download all (3 files, .tar.gz)

v3-0001-Set-HEAP_XMAX_INVALID-for-aborted-updates-of-tupl.patch (text/x-patch, 6.4K) download From 4c12deb22a1516c9b8af4f279ba8ad7f8d98dc17 Mon Sep 17 00:00:00 2001 From: Manu <manuelreyesbravo@gmail.com> Date: Mon, 26 Oct 2026 12:00:00 -0300 Subject: [PATCH v3 1/2] Set HEAP_XMAX_INVALID for aborted updates of tuples with a running inserter e7f4463e869 made HeapTupleSatisfiesVacuumHorizon() set HEAP_XMAX_INVALID on a tuple inserted by the current transaction and updated by one of its subtransactions that rolled back. The same tuple is seen by other backends while the inserting transaction is still running, and there the hint was not set either. rewriteheap.c then treats the tuple as updated, so a VACUUM FULL or CLUSTER of a catalog run by another session loses the row: for example, the pg_class row of a table created in an open transaction, after an ALTER TABLE on it was rolled back to a savepoint. The hint is now also set in the branch where the inserter is in progress, if the updater has aborted. This is skipped when xmax is equal to xmin, as xmax is then in progress as well, and when xmax is a MultiXact. The clog is asked first, as a cheap filter for an updater that is still running, and the proc array has to confirm that the updater is gone: an abort reaches the clog before the transaction leaves the proc array, and until then the new version of the tuple, whose xmin is this xmax, is still in progress. Setting the hint from the clog alone would leave the old version marked as not updated while its successor is still in progress. The xmin != xmax check and the race with the proc array come from Zhijie Hou, and the MultiXact skip from Michael Paquier. The isolation test is from Shihao Zhong. Author: Manu <manuelreyesbravo@gmail.com> Author: Shihao Zhong <zhong950419@gmail.com> Reviewed-by: Zhijie Hou <houzhijie22@gmail.com> Discussion: https://postgr.es/m/19760-8f9a0eba17bc8c92@postgresql.org Backpatch-through: 14 --- src/backend/access/heap/heapam_visibility.c | 29 +++++++++++++++++ .../cluster-aborted-subxact-update.out | 20 ++++++++++++ src/test/isolation/isolation_schedule | 1 + .../specs/cluster-aborted-subxact-update.spec | 32 +++++++++++++++++++ 4 files changed, 82 insertions(+) create mode 100644 src/test/isolation/expected/cluster-aborted-subxact-update.out create mode 100644 src/test/isolation/specs/cluster-aborted-subxact-update.spec diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/access/heap/heapam_visibility.c index 905444a..681aaed 100644 --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -1184,6 +1184,35 @@ HeapTupleSatisfiesVacuumHorizon(HeapTuple htup, Buffer buffer, TransactionId *de } else if (TransactionIdIsInProgress(HeapTupleHeaderGetRawXmin(tuple))) { + /* + * If the updater of the tuple has already aborted (a rolled-back + * subtransaction of the inserter), remember that, as callers like + * rewriteheap.c rely on HEAP_XMAX_INVALID to tell a live tuple + * from an updated one. + */ + if (!(tuple->t_infomask & HEAP_XMAX_INVALID) && + !(tuple->t_infomask & HEAP_XMAX_IS_MULTI) && + !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) && + !HeapTupleHeaderIsOnlyLocked(tuple)) + { + TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple); + + /* + * xmax == xmin: xmax is in progress too, nothing to do. + * The clog is only a cheap filter here: a running xmax is not + * aborted there. An abort reaches the clog before the xid + * leaves the proc array, and the new version of the tuple, + * whose xmin is this xmax, is in progress until then. So the + * proc array must confirm it, as the note at the top of this + * file asks. + */ + if (!TransactionIdEquals(xmax, HeapTupleHeaderGetRawXmin(tuple)) && + TransactionIdDidAbort(xmax) && + !TransactionIdIsInProgress(xmax)) + SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, + InvalidTransactionId); + } + /* * It'd be possible to discern between INSERT/DELETE in progress * here by looking at xmax - but that doesn't seem beneficial for diff --git a/src/test/isolation/expected/cluster-aborted-subxact-update.out b/src/test/isolation/expected/cluster-aborted-subxact-update.out new file mode 100644 index 0000000..a3448c2 --- /dev/null +++ b/src/test/isolation/expected/cluster-aborted-subxact-update.out @@ -0,0 +1,20 @@ +Parsed test spec with 2 sessions + +starting permutation: create_and_rollback_alter vacuum_full_pg_class commit check_pg_class +step create_and_rollback_alter: + BEGIN; + CREATE TABLE vacfull_tab (a int); + SAVEPOINT sp; + ALTER TABLE vacfull_tab ADD COLUMN b int; + ROLLBACK TO sp; + +step vacuum_full_pg_class: VACUUM FULL pg_class; +step commit: COMMIT; +step check_pg_class: + SELECT relname, relnatts FROM pg_class WHERE relname = 'vacfull_tab'; + +relname |relnatts +-----------+-------- +vacfull_tab| 1 +(1 row) + diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index bf9a203..eee40f5 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -122,6 +122,7 @@ test: plpgsql-toast test: cluster-conflict test: cluster-conflict-partition test: cluster-toast-value-reuse +test: cluster-aborted-subxact-update test: truncate-conflict test: serializable-parallel test: serializable-parallel-2 diff --git a/src/test/isolation/specs/cluster-aborted-subxact-update.spec b/src/test/isolation/specs/cluster-aborted-subxact-update.spec new file mode 100644 index 0000000..e2000ed --- /dev/null +++ b/src/test/isolation/specs/cluster-aborted-subxact-update.spec @@ -0,0 +1,32 @@ +# VACUUM FULL must keep a row whose inserting transaction is still running, +# when a rolled-back subtransaction of that transaction updated the row. +# +# VACUUM FULL of a user table would wait for the inserter, so this uses +# pg_class. The inserter creates a table and adds a column in a subtransaction +# that it rolls back, which leaves an updated pg_class row whose updater +# aborted. + +teardown +{ + DROP TABLE IF EXISTS vacfull_tab; +} + +session creator +step create_and_rollback_alter +{ + BEGIN; + CREATE TABLE vacfull_tab (a int); + SAVEPOINT sp; + ALTER TABLE vacfull_tab ADD COLUMN b int; + ROLLBACK TO sp; +} +step commit { COMMIT; } + +session vacuumer +step vacuum_full_pg_class { VACUUM FULL pg_class; } +step check_pg_class +{ + SELECT relname, relnatts FROM pg_class WHERE relname = 'vacfull_tab'; +} + +permutation create_and_rollback_alter vacuum_full_pg_class commit check_pg_class -- 2.56.0
v3-0002-Test-the-clog-proc-array-window-in-HeapTupleSatis.patch (text/x-patch, 3.6K) download From 378472c1b3b2ca9f756a7bb09032db5729b73801 Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Mon, 26 Oct 2026 12:00:00 -0300 Subject: [PATCH v3 2/2] Test the clog/proc array window in HeapTupleSatisfiesVacuumHorizon() A HOT update in a subtransaction, released, then a ROLLBACK stopped after the clog update but before the proc array update, with the injection point transaction-abort-after-clog. In that window ANALYZE must not mark the old tuple HEAP_XMAX_INVALID while the new heap-only tuple is still in progress, or brin_summarize_range() fails with "failed to find parent tuple for heap-only tuple". Author: Zhijie Hou <houzhijie22@gmail.com> Discussion: https://postgr.es/m/19760-8f9a0eba17bc8c92@postgresql.org Backpatch-through: 17 --- .../expected/multixact-aborted-updater.out | 30 +++++++++++++++++++ .../specs/multixact-aborted-updater.spec | 19 ++++++++++++ 2 files changed, 49 insertions(+) diff --git a/src/test/modules/injection_points/expected/multixact-aborted-updater.out b/src/test/modules/injection_points/expected/multixact-aborted-updater.out index 4075071..abc3d0a 100644 --- a/src/test/modules/injection_points/expected/multixact-aborted-updater.out +++ b/src/test/modules/injection_points/expected/multixact-aborted-updater.out @@ -82,3 +82,33 @@ id|filler 2|initial (1 row) + +starting permutation: s1ddl s1abort s3brin wake +step s1ddl: + BEGIN; + INSERT INTO mxact_abort VALUES (2, 'v1'); + SAVEPOINT sp; + UPDATE mxact_abort SET filler = 'v2' WHERE id = 2; + RELEASE SAVEPOINT sp; + +step s1abort: ROLLBACK; <waiting ...> +step s3brin: + SELECT brin_desummarize_range('mxact_brin_idx', 0); + ANALYZE mxact_abort; + SELECT brin_summarize_range('mxact_brin_idx', 0); + +brin_desummarize_range +---------------------- + +(1 row) + +brin_summarize_range +-------------------- + 1 +(1 row) + +step wake: + SELECT FROM injection_points_detach('transaction-abort-after-clog'); + SELECT FROM injection_points_wakeup('transaction-abort-after-clog'); + +step s1abort: <... completed> diff --git a/src/test/modules/injection_points/specs/multixact-aborted-updater.spec b/src/test/modules/injection_points/specs/multixact-aborted-updater.spec index 98d265a..f4c6fc9 100644 --- a/src/test/modules/injection_points/specs/multixact-aborted-updater.spec +++ b/src/test/modules/injection_points/specs/multixact-aborted-updater.spec @@ -20,6 +20,7 @@ setup CREATE EXTENSION injection_points; CREATE TABLE mxact_abort (id int PRIMARY KEY, filler text); + CREATE INDEX mxact_brin_idx ON mxact_abort USING brin (id); INSERT INTO mxact_abort VALUES (1, 'initial'); } @@ -38,6 +39,13 @@ setup { } step s1begin { BEGIN; } step s1update { UPDATE mxact_abort SET filler = 's1' WHERE id = 1; } +step s1ddl { + BEGIN; + INSERT INTO mxact_abort VALUES (2, 'v1'); + SAVEPOINT sp; + UPDATE mxact_abort SET filler = 'v2' WHERE id = 2; + RELEASE SAVEPOINT sp; +} step s1abort { ROLLBACK; } # Transaction 2 locks the row in KEY SHARE mode, to cause a multixact to be @@ -52,6 +60,11 @@ step s3update { UPDATE mxact_abort SET filler = 's3' WHERE id = 1; } step s3delete { DELETE FROM mxact_abort WHERE id = 1; } step s3keyupdate { UPDATE mxact_abort SET id = 2 WHERE id = 1; } step s3check { SELECT * FROM mxact_abort ORDER BY id; } +step s3brin { + SELECT brin_desummarize_range('mxact_brin_idx', 0); + ANALYZE mxact_abort; + SELECT brin_summarize_range('mxact_brin_idx', 0); +} session waker step wake { @@ -105,3 +118,9 @@ permutation s3keyupdate wake s3check + +permutation + s1ddl + s1abort + s3brin + wake -- 2.56.0
measurements.txt (text/plain, 3.4K) download BUG #19760, part 2: VACUUM cost of the second HEAP_XMAX_INVALID hint One run on 2026-10-25, 12 interleaved rounds per build, with Michael's scripts from upthread (setup.sql, holders.sql, delete.sql, subxact_updates.sql, vacuum.sql), unchanged, plus subxact_aborts.sql below. Same harness and method as the measurements.txt of 2026-10-22. Base: master 871dee0ccbe, the base of those measurements. Builds: configure --enable-depend, CFLAGS=-O2, no assertions. HEAD master v2+0001 v2-0001 from upthread + nocfbot-0001 (xmin != xmax, MultiXact skip): the second hint with v2's check, !TransactionIdIsInProgress() && !TransactionIdDidCommit() old 0002 + the clog-only TransactionIdDidAbort() check v3 + the fixed check: TransactionIdDidAbort(xmax) && !TransactionIdIsInProgress(xmax) The heapam_visibility.c of "v3" is the one of v3-0001 on today's master, byte for byte, except for two lines of a test-only injection point that these builds do not compile in (the object code differs only in the __LINE__ constants of two elog() calls). Machine: Ryzen AI 9 HX 370 laptop, runs pinned to 4 Zen 5 cores (8 threads); data directory on tmpfs. Server settings: max_connections = 200, autovacuum = off, max_wal_size = 256MB. 64 holders. Metric: per sample, the mean of the 9 VACUUM times of vacuum.sql (or the first one / the other 8, where said). Reported: median of the samples, ratio to HEAD, bootstrap 95% CI of the ratio of medians (4000 resamples). A sample is dropped and redone if a CPU calibration loop run before and after it is more than 8% slower than its idle time. delete.sql: build n median ms x HEAD 95% CI HEAD 12 418.9 1.00 v2+0001 11 419.0 1.00 0.99..1.01 old 0002 11 417.8 1.00 0.99..1.00 v3 12 419.7 1.00 0.99..1.01 subxact_updates.sql: build n median ms x HEAD 95% CI HEAD 9 923.6 1.00 v2+0001 9 1396.6 1.51 1.50..1.55 old 0002 9 931.5 1.01 0.99..1.03 v3 9 924.8 1.00 0.99..1.02 subxact_aborts.sql, first VACUUM only: build n median ms x HEAD 95% CI HEAD 12 5869.6 1.00 v2+0001 12 6295.6 1.07 1.07..1.07 old 0002 12 5877.2 1.00 1.00..1.00 v3 12 6294.1 1.07 1.07..1.07 subxact_aborts.sql, VACUUMs 2-9: build n median ms x HEAD 95% CI HEAD 12 5430.5 1.00 v2+0001 12 5431.9 1.00 1.00..1.00 old 0002 12 5434.1 1.00 1.00..1.00 v3 12 5430.5 1.00 1.00..1.00 In subxact_updates.sql xmax is a released subtransaction that is still running: it is not aborted in the clog, so v3 never asks the proc array. In subxact_aborts.sql xmax has aborted: v3 pays the proc array check once per tuple, in the first VACUUM, which sets the hint. subxact_updates.sql had 3 rounds with no clean sample (n = 9). subxact_aborts.sql (like subxact_updates.sql, but each subtransaction rolls back): \if :{?n} \else \set n 500000 \endif BEGIN; INSERT INTO t SELECT g, 0 FROM generate_series(1, :n) g; DO $$ DECLARE r record; BEGIN FOR r IN SELECT ctid AS c FROM t LOOP BEGIN UPDATE t SET v = 1 WHERE ctid = r.c; RAISE EXCEPTION 'roll back this subtransaction'; EXCEPTION WHEN others THEN NULL; END; END LOOP; END $$;
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-24 12:57                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-26 00:57                               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-26 15:41                                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
@ 2026-10-26 23:34                                   ` Michael Paquier <michael@paquier.xyz>
  2026-10-26 23:43                                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-10-26 23:34 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Mon, Oct 26, 2026 at 12:41:19PM -0300, Manu wrote:
> Thanks for taking care of it.  The attached series adds the second
> spot on top of e7f4463e869, as shihao asked upthread.
>
> +            /*
> +             * If the updater of the tuple has already aborted (a rolled-back
> +             * subtransaction of the inserter), remember that, as callers like
> +             * rewriteheap.c rely on HEAP_XMAX_INVALID to tell a live tuple
> +             * from an updated one.
> +             */
> +            if (!(tuple->t_infomask & HEAP_XMAX_INVALID) &&
> +                !(tuple->t_infomask & HEAP_XMAX_IS_MULTI) &&
> +                !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) &&
> +                !HeapTupleHeaderIsOnlyLocked(tuple))
> +            {
> +                TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple);
> +
> +                /*
> +                 * xmax == xmin: xmax is in progress too, nothing to do.
> +                 * The clog is only a cheap filter here: a running xmax is not
> +                 * aborted there.  An abort reaches the clog before the xid
> +                 * leaves the proc array, and the new version of the tuple,
> +                 * whose xmin is this xmax, is in progress until then.  So the
> +                 * proc array must confirm it, as the note at the top of this
> +                 * file asks.
> +                 */
> +                if (!TransactionIdEquals(xmax, HeapTupleHeaderGetRawXmin(tuple)) &&
> +                    TransactionIdDidAbort(xmax) &&
> +                    !TransactionIdIsInProgress(xmax))
> +                    SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
> +                                InvalidTransactionId);
> +            }

FWIW, this does not feel right.  Your patch is a gathering of
everybody's suggestions that have been fed to an LLM, without thinking
through the problem, resulting of a pile of changes which was itself a
pile of changes.  I'd suggest to take three deep breaths and not rely
on an LLM to try to think through all the potential holes that any of
the previous suggestions have.  Piling workarounds on top of
workarounds is no good for these code paths.  They just become more
complicated to think about long-term.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-24 12:57                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-26 00:57                               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-26 15:41                                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  2026-10-26 23:34                                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-26 23:43                                     ` Manu <manuelreyesbravo@gmail.com>
  2026-10-27 20:03                                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Manu @ 2026-10-26 23:43 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Michael,

On Tue, Oct 27, 2026 at 08:34:41AM +0900, Michael Paquier wrote:
> Piling workarounds on top of workarounds is no good for these code
> paths.  They just become more complicated to think about long-term.

Fair enough. The patch grew by adding each condition as it came up in
the thread, not from a clear account of when that hint is safe to
set, and Zhijie's race shows how fragile that is. I'll withdraw it.
Your commit fixes the reported case. If I come back to the second
spot, it will start from that account, not from another variant.

Manu






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 08:53                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 23:42                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-24 12:57                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-26 00:57                               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-26 15:41                                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  2026-10-26 23:34                                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-26 23:43                                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
@ 2026-10-27 20:03                                       ` Manu <manuelreyesbravo@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Manu @ 2026-10-27 20:03 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Michael,

When I withdrew the patch, I said that if I came back to this, it
would start from an account of the problem and not from another
variant.  Here it is.

There is a case that no hint bit can prevent: the pg_class row of a
table committed long ago is lost when the subtransaction that
updated it aborts while VACUUM FULL pg_class is between the old and
the new version.  The attached script reproduces it with plain SQL,
using the progress view to time the ROLLBACK TO.  It loses the row
in every round on unmodified master, REL_18_STABLE and
REL_14_STABLE.

When the scan reads the old version, its updater is still running,
so there is nothing to set yet.  heapam_relation_copy_for_cluster()
is right to find the new version dead, as its inserter aborted.
What is wrong is what rewrite_heap_dead_tuple() concludes from that:
that the old version is dead too, which only holds if the update
committed.  pruneheap.c draws that conclusion only from a
RECENTLY_DEAD tuple.

So instead of one more condition in rewrite_heap_dead_tuple(), the
fix removes it: dead tuples are no longer passed to rewriteheap.c,
and a tuple left waiting for a version that was never written is
resolved in end_heap_rewrite(), once the outcome of its update is
final.  The code change removes more than it adds (71+, 77-); the
tests are a separate patch.  I can post both if you want to look.

Manu

Attachments:

vacuum-full-lost-pg-class-row.sh.txt (text/plain, 5.3K) download #!/bin/bash # repro-case3-aimed.sh: VACUUM FULL pg_class loses the pg_class row of a committed table when # the subtransaction that updated that row aborts during the rewrite. No patch, no debugger. # # s1 updates victim's pg_class row in a subtransaction, then busy-waits on the progress of the # VACUUM FULL pg_class that s2 starts (phase "seq scanning heap" and heap_blks_scanned past the # block of the old version A). At that point the rewrite # has already read A while the updater was running. s1 runs ROLLBACK TO and reads the progress # again, which shows that the scan has not reached the block of the new version B yet (B goes # to the last block of the heap). Each round uses a fresh victim, created before the fillers. # # Works on master (progress command 'REPACK') and on REL_14_STABLE..REL_18_STABLE ('CLUSTER'). # The parameter slots differ; pg_stat_get_progress_info() returns pid, datid, relid and then # st_progress_param[i] as param(i+1), so (src/include/commands/progress.h of each branch): # master, REPACK: PROGRESS_REPACK_PHASE 1 -> param2 # PROGRESS_REPACK_TOTAL_HEAP_BLKS 7 -> param8, # PROGRESS_REPACK_HEAP_BLKS_SCANNED 8 -> param9 # 14..18, CLUSTER: PROGRESS_CLUSTER_PHASE 1 -> param2 # PROGRESS_CLUSTER_TOTAL_HEAP_BLKS 5 -> param6, # PROGRESS_CLUSTER_HEAP_BLKS_SCANNED 6 -> param7 # In both, phase 1 is "seq scanning heap" (PROGRESS_*_PHASE_SEQ_SCAN_HEAP). # # Usage: repro-case3-aimed.sh <pg bin dir> <work dir> [port] [ntables] [rounds] # <work dir> is removed and recreated; it holds a scratch cluster. set -uo pipefail BIN=$1; W=$2; PORT=${3:-61340}; NTABLES=${4:-20000}; ROUNDS=${5:-5} D=$W/data; LOG=$W/server.log export PGHOST=$W PGPORT=$PORT PGDATABASE=postgres PSQL="$BIN/psql -X -q -At -v ON_ERROR_STOP=1" rm -rf "$W"; mkdir -p "$W" "$BIN/initdb" -D "$D" -N -A trust > "$W/initdb.log" 2>&1 || { echo "initdb failed"; exit 2; } cat >> "$D/postgresql.conf" <<CONF port = $PORT unix_socket_directories = '$W' listen_addresses = '' fsync = off autovacuum = off CONF "$BIN/pg_ctl" -D "$D" -l "$LOG" -w start > /dev/null || { echo "start failed"; exit 2; } trap '"$BIN/pg_ctl" -D "$D" -m immediate stop > /dev/null 2>&1' EXIT # (count(*) so that the function really runs; the old branches reject 'REPACK' with an ERROR) if $PSQL -c "SELECT count(*) FROM pg_stat_get_progress_info('REPACK')" > /dev/null 2>&1; then CMD=REPACK; TOT=param8; SCAN=param9 else CMD=CLUSTER; TOT=param6; SCAN=param7 fi echo "server: $($PSQL -c 'SELECT version()' | cut -d, -f1); progress: '$CMD', phase param2," \ "heap_blks_total $TOT, heap_blks_scanned $SCAN" for r in $(seq 1 "$ROUNDS"); do $PSQL -c "CREATE TABLE victim_$r (a int, b text)" || exit 2; done for ((i = 1; i <= NTABLES; i += 1000)); do $PSQL -c "DO \$\$ BEGIN FOR i IN $i..$((i + 999 < NTABLES ? i + 999 : NTABLES)) LOOP EXECUTE format('CREATE TABLE filler_%s ()', i); END LOOP; END \$\$" || exit 2 done $PSQL -c "VACUUM FULL pg_class" || exit 2 echo "setup: $ROUNDS victims, $NTABLES filler tables" PROG="SELECT 'phase ' || param2 || ', heap_blks_scanned ' || $SCAN || ' of ' || $TOT FROM pg_stat_get_progress_info('$CMD') WHERE relid = 1259" lost=0 for r in $(seq 1 "$ROUNDS"); do rm -f "$W/altered" VOID=$($PSQL -c "SELECT oid FROM pg_class WHERE relname = 'victim_$r'") read -r BLK < <($PSQL -c "SELECT (ctid::text::point)[0]::int FROM pg_class WHERE oid = $VOID") NB=$($PSQL -c "SELECT pg_relation_size('pg_class') / 8192") echo "round $r: victim_$r oid $VOID, xmin $($PSQL -c "SELECT xmin FROM pg_class WHERE oid = $VOID")," \ "A in block $BLK of $NB (B will go to the last block)" $PSQL > "$W/s1-$r.out" 2>&1 <<SQL & -- warm the caches used below, so nothing in s1 needs pg_class while VACUUM FULL runs SELECT pg_stat_clear_snapshot(); $PROG; DO \$\$ DECLARE ph int8; sc int8; BEGIN SELECT param2, $SCAN INTO ph, sc FROM pg_stat_get_progress_info('$CMD') WHERE relid = 1259; END \$\$; BEGIN; SAVEPOINT sp; ALTER TABLE victim_$r SET (fillfactor = 50); -- (no query on pg_class from here on: it would keep a lock that blocks VACUUM FULL) SELECT 's1: subxact ' || pg_current_xact_id() || ' updated victim_$r'; \! touch $W/altered DO \$\$ DECLARE ph int8; sc int8; n int8 := 0; BEGIN LOOP PERFORM pg_stat_clear_snapshot(); SELECT param2, $SCAN INTO ph, sc FROM pg_stat_get_progress_info('$CMD') WHERE relid = 1259; EXIT WHEN ph = 1 AND sc > $BLK; n := n + 1; IF n > 20000000 THEN RAISE EXCEPTION 'scan never passed block $BLK'; END IF; END LOOP; END \$\$; SELECT pg_stat_clear_snapshot(); SELECT 's1: before ROLLBACK TO: ' || ($PROG); ROLLBACK TO sp; SELECT pg_stat_clear_snapshot(); SELECT 's1: after ROLLBACK TO: ' || coalesce(($PROG), 'VACUUM FULL already finished'); COMMIT; SQL S1=$! while [ ! -f "$W/altered" ]; do kill -0 $S1 2> /dev/null || { echo "s1 failed:"; cat "$W/s1-$r.out"; exit 2; } sleep 0.01 done $PSQL -c "VACUUM FULL pg_class" 2>&1 wait $S1 R=$($PSQL -c "SELECT count(*) FROM pg_class WHERE oid = $VOID") grep '^s1:' "$W/s1-$r.out" | sed 's/^s1: / /' echo " victim_$r rows in pg_class after VACUUM FULL: $R" grep -v '^s1:\|^$\|^phase' "$W/s1-$r.out" | grep . | sed 's/^/ s1 says: /' [ "$R" = 1 ] || lost=$((lost + 1)) done echo "RESULT: pg_class row lost in $lost of $ROUNDS rounds"
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
@ 2026-10-22 20:44                         ` Manu <manuelreyesbravo@gmail.com>
  2026-10-25 08:31                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  3 siblings, 1 reply; 30+ messages in thread

From: Manu @ 2026-10-22 20:44 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Zhijie Hou <houzhijie22@gmail.com>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Michael,

On Thu, Oct 22, 2026 at 11:22:08AM +0900, Michael Paquier wrote:
> The DELETE case is a much more common case.  It regresses even more
> badly, and that does not look acceptable to me.  That should be
> optimizable, of course, I don't really know yet how to do that.

I ran your scripts as attached upthread, on master 871dee0ccbe with
v2, 500k rows and 64 holders, 12 interleaved rounds per build.  The
ratios match yours: VACUUM takes 1.98x HEAD with delete.sql (95% CI
1.96..2.02) and 1.53x with subxact_updates.sql (1.51..1.55), and
insert_only.sql does not move.  Method and tables are in the attached
measurements.txt.

The two cases cost for different reasons.  In delete.sql xmax is the
inserter's own xid, so the new TransactionIdIsInProgress(xmax) is a
second proc array scan, and pg_stat_slru shows no extra access.  It
grows with the number of open transactions: 1.51x with no holder,
1.95x with 16, 2.02x with 128.  In subxact_updates.sql xmax is one of
500k subxids, the subxid cache has overflowed, and v2 doubles the
pg_subtrans and clog accesses of each VACUUM (999872 to 1999744 for
pg_subtrans, 499936 to 999872 for the clog).

Zhijie's idea, doing the check only when xmin != xmax, removes the
delete.sql regression: 1.00x (0.99..1.01).  In delete.sql xmax and
xmin are the same xid, so the check is skipped there.  subxact_updates
stays at 1.53x, as he expected.  The condition the other way around
loses the pg_class row again.  On its own the idea keeps the
MultiXact race of his repro; with the HEAP_XMAX_IS_MULTI skip you
suggested, the repro passes.  Both together are nocfbot-0001.

For subxact_updates, nocfbot-0002 on top of it.  In that branch xmin
is in progress, so xmax is the inserter's xid or one of its
subtransactions, and RecordTransactionAbort() writes a subtransaction
abort to the clog before the xids leave the proc array.  So
TransactionIdDidAbort(xmax) gives the same answer there as
!TransactionIdIsInProgress() && !TransactionIdDidCommit(), with one
clog lookup and no proc array or pg_subtrans access.  With both
diffs, subxact_updates is at 1.01x (1.00..1.03) with 64 holders and
1.00x with 128.  What 0002 costs is that clog lookup per tuple:
1.14x with no holder at all (50 ms to 57 ms).

With 0001 alone and with 0001 + 0002, these pass: the v2-0002 spec,
your pg_statistic sequence, VACUUM FULL pg_class with the CREATE
TABLE transaction open, logical decoding across it, Zhijie's
injection point repro, make check, isolation, and recovery 035.
Since 0002 changes when the hint is set, I added five cases where
the pg_class row must survive VACUUM FULL pg_class exactly once
(released, nested and prepared subtransactions).  A build that sets
the hint unconditionally passes the original SQL cases and fails
three of the new ones, with two pg_class rows for the table.

The diffs apply on top of v2-0001 and v2-0002.  Shihao, if they hold
up, feel free to fold them into a v3.

Regards,
Manu

Attachments:

measurements.txt (text/plain, 6.8K) download BUG #19760: cost of the second SetHintBits() of v2-0001, and two diffs on top of v2 Run with Michael's scripts from upthread, as attached there (setup.sql, holders.sql, delete.sql, insert_only.sql, subxact_updates.sql, vacuum.sql), unchanged: 500k rows, 64 holders. Base: master 871dee0ccbe, plus v2-0001 and v2-0002 from upthread. Builds: configure --enable-depend, CFLAGS=-O2, no assertions. Machine: Ryzen AI 9 HX 370 laptop, runs pinned to 4 Zen 5 cores (8 threads); data directory on tmpfs. Server settings: max_connections = 200, autovacuum = off, max_wal_size = 256MB, the rest default. One sample: psql -f setup.sql for i in $(seq $H); do psql -X -f holders.sql > /dev/null 2>&1 & done (wait until $H backends sleep in pg_sleep) psql: \i <load>.sql -- left open CHECKPOINT; SELECT pg_stat_reset_slru(); psql -X -f vacuum.sql -- metric: mean of its 9 VACUUM times read pg_stat_slru, then stop everything Each round runs every build once, in an order rotated per round. A sample is dropped and redone if a CPU calibration loop run before and after it is more than 8% slower than its idle time. Reported: median of the samples, ratio to HEAD, bootstrap 95% CI of the ratio of medians (4000 resamples). Builds: HEAD master v2 v2-0001 + v2-0002 as posted v2 1st hint only v2 without the second SetHintBits() v2 + skip xmax==xmin second hint only when xmax != xmin v2 + skip xmax!=xmin second hint only when xmax == xmin v2 + catalogs only second hint only if IsCatalogRelationOid(htup->t_tableOid) v2 + DidAbort second hint if TransactionIdDidAbort(xmax), instead of !InProgress && !DidCommit v2 + 0001 skip xmax == xmin and MultiXact xmax v2 + 0001 + 0002 0001, then TransactionIdDidAbort(xmax) delete.sql, 64 holders: build n median ms x HEAD 95% CI HEAD 12 424.4 1.00 v2 12 841.2 1.98 1.96..2.02 v2 1st hint only 12 420.0 0.99 0.98..1.01 v2 + skip xmax==xmin 12 424.2 1.00 0.99..1.01 v2 + skip xmax!=xmin 12 838.1 1.98 1.95..2.05 v2 + catalogs only 12 422.6 1.00 0.99..1.01 v2 + DidAbort 12 429.4 1.01 1.00..1.03 v2 + 0001 12 424.1 1.00 0.99..1.02 v2 + 0001 + 0002 12 423.7 1.00 0.98..1.02 subxact_updates.sql, 64 holders: build n median ms x HEAD 95% CI HEAD 12 875.1 1.00 v2 12 1338.3 1.53 1.51..1.55 v2 1st hint only 11 876.0 1.00 0.99..1.01 v2 + skip xmax==xmin 11 1335.0 1.53 1.51..1.56 v2 + skip xmax!=xmin 12 877.7 1.00 0.99..1.03 v2 + catalogs only 12 882.1 1.01 1.00..1.03 v2 + DidAbort 11 884.6 1.01 1.00..1.02 v2 + 0001 12 1336.0 1.53 1.51..1.55 v2 + 0001 + 0002 12 883.4 1.01 1.00..1.03 insert_only.sql, 64 holders: build n median ms x HEAD 95% CI HEAD 12 419.6 1.00 v2 12 418.8 1.00 0.99..1.01 v2 1st hint only 12 416.2 0.99 0.98..1.00 v2 + skip xmax==xmin 12 417.7 1.00 0.99..1.01 v2 + skip xmax!=xmin 12 418.4 1.00 0.99..1.01 v2 + catalogs only 12 419.6 1.00 0.99..1.01 v2 + DidAbort 12 419.8 1.00 0.99..1.01 v2 + 0001 12 417.5 1.00 0.99..1.01 v2 + 0001 + 0002 12 416.7 0.99 0.98..1.01 pg_stat_slru blks_hit+blks_read per VACUUM, delete.sql, 64 holders: build clog subtrans HEAD 0 0 v2 0 0 v2 + 0001 + 0002 0 0 pg_stat_slru blks_hit+blks_read per VACUUM, subxact_updates.sql, 64 holders: build clog subtrans HEAD 499936 999872 v2 999872 1999744 v2 + 0001 + 0002 999936 999872 delete.sql, holders sweep (median ms, x HEAD): holders HEAD v2 v2 + 0001 v2+0001+0002 0 12 17 (1.51) 12 (1.03) 12 (1.02) 16 117 229 (1.95) 118 (1.01) 120 (1.02) 64 424 841 (1.98) 424 (1.00) 424 (1.00) 128 823 1659 (2.02) 823 (1.00) 836 (1.01) subxact_updates.sql, holders sweep (median ms, x HEAD): holders HEAD v2 v2 + 0001 v2+0001+0002 0 50 83 (1.65) 83 (1.67) 57 (1.14) 16 268 412 (1.54) 412 (1.54) 276 (1.03) 64 875 1338 (1.53) 1336 (1.53) 883 (1.01) 128 1748 2644 (1.51) 2645 (1.51) 1756 (1.00) Correctness, on the same variants built with --enable-cassert --enable-injection-points: 1 v2-0002 isolation spec, both permutations 2 Michael's ANALYZE + CLUSTER pg_statistic sequence: 1 row 3 VACUUM FULL pg_class while the CREATE TABLE transaction is open, after ALTER TABLE rolled back to a savepoint: the pg_class row survives the COMMIT 4 Zhijie's MultiXact / FOR KEY SHARE repro (his injection point and spec): judged on the last step, FOR UPDATE NOWAIT must fail while FOR KEY SHARE is held 5 logical decoding (test_decoding) across case 3 plus an INSERT: both INSERTs decoded, no error 6 make check, isolation check, recovery 035 Same as 3, the pg_class row must exist once with the right relnatts: 7 ALTER in a subtransaction that was released 8 ALTER in an inner savepoint, released, then the outer one rolled back 9 as 3, then PREPARE TRANSACTION before the VACUUM FULL 10 as 7, then PREPARE TRANSACTION before the VACUUM FULL 11 one ALTER released, then a second ALTER rolled back build 1 2 3 4 5 6 7 8 9 10 11 HEAD F F F . F . . F F . F v2 . . . F . . . . . . . v2 1st hint only F . F . F . . F F . F v2 + skip xmax==xmin . . . F . . . . . . . v2 + skip xmax!=xmin F . F . F . . F F . F v2 + catalogs only . . . * . . . . . . . v2 + DidAbort . . . F . . . . . . . v2 + 0001 . . . . . . . . . . . v2 + 0001 + 0002 . . . . . . . . . . . (. pass, F fail) * passes only because the repro table is not a catalog, so the new code is never reached; on a catalog it keeps v2's race. Mutant check: 0001 + 0002 with the hint set unconditionally passes cases 2, 3 and 5, and fails 7, 10 and 11 with two pg_class rows for the table (old and new version both kept).
nocfbot-0001-skip-equal-xmax-and-multi-on-v2.diff.txt (text/plain, 1.1K) download Applies on top of v2-0001 and v2-0002 upthread. Not for the CF bot. Skip xmax == xmin and MultiXact xmax in the second hint. diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/access/heap/heapam_visibility.c --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -1191,12 +1191,15 @@ HeapTupleSatisfiesVacuumHorizon(HeapTuple htup, Buffer buffer, TransactionId *de * from an updated one. */ if (!(tuple->t_infomask & HEAP_XMAX_INVALID) && + !(tuple->t_infomask & HEAP_XMAX_IS_MULTI) && !HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask) && !HeapTupleHeaderIsOnlyLocked(tuple)) { TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple); - if (!TransactionIdIsInProgress(xmax) && + /* xmax == xmin: xmax is in progress too, nothing to do */ + if (!TransactionIdEquals(xmax, HeapTupleHeaderGetRawXmin(tuple)) && + !TransactionIdIsInProgress(xmax) && !TransactionIdDidCommit(xmax)) SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, InvalidTransactionId);
nocfbot-0002-clog-only-abort-check-on-v2.diff.txt (text/plain, 1.1K) download Applies on top of v2 and nocfbot-0001. Not for the CF bot. Ask the clog only: TransactionIdDidAbort() on xmax. diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/access/heap/heapam_visibility.c --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -1197,10 +1197,15 @@ HeapTupleSatisfiesVacuumHorizon(HeapTuple htup, Buffer buffer, TransactionId *de { TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple); - /* xmax == xmin: xmax is in progress too, nothing to do */ + /* + * xmin is in progress, so xmax is in the inserter's + * transaction tree. If xmax == xmin, it is in progress + * too. Otherwise it is running or it is a subtransaction + * that aborted, and the abort reaches the clog before the + * subtransaction leaves the proc array: the clog tells. + */ if (!TransactionIdEquals(xmax, HeapTupleHeaderGetRawXmin(tuple)) && - !TransactionIdIsInProgress(xmax) && - !TransactionIdDidCommit(xmax)) + TransactionIdDidAbort(xmax)) SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, InvalidTransactionId); }
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 20:44                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
@ 2026-10-25 08:31                           ` Zhijie Hou <houzhijie22@gmail.com>
  2026-10-25 22:58                             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Zhijie Hou @ 2026-10-25 08:31 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Fri, Oct 23, 2026 at 4:44 AM Manu <manuelreyesbravo@gmail.com> wrote:
>
> On Thu, Oct 22, 2026 at 11:22:08AM +0900, Michael Paquier wrote:
> > The DELETE case is a much more common case.  It regresses even more
> > badly, and that does not look acceptable to me.  That should be
> > optimizable, of course, I don't really know yet how to do that.
>
>
> For subxact_updates, nocfbot-0002 on top of it.  In that branch xmin
> is in progress, so xmax is the inserter's xid or one of its
> subtransactions, and RecordTransactionAbort() writes a subtransaction
> abort to the clog before the xids leave the proc array.  So
> TransactionIdDidAbort(xmax) gives the same answer there as
> !TransactionIdIsInProgress() && !TransactionIdDidCommit(), with one
> clog lookup and no proc array or pg_subtrans access.

This doesn't look safe to me. Even if TransactionIdDidAbort() returns true, the
xmax may not have been removed from PGPROC yet. So this change would create the
risky case where the old tuple has been marked as XMAX_INVAL while the new
tuple's xmin is still considered in progress.

After some testing, I confirmed that this causes an issue with HOT updates:

When a tuple is HOT-updated in a subtransaction and the transaction then rolls
back, there is a window after the XIDs are marked aborted in CLOG but before
they are removed from ProcArray. If a concurrent ANALYZE scans the table during
that window, HeapTupleSatisfiesVacuumHorizon() marks the old tuple as
HEAP_XMAX_INVALID while still treating the new heap-only tuple as in progress.
If brin_summarize_range() then scans the page in that same window,
heap_get_root_tuples() does not follow the HOT chain from the old tuple because
HeapTupleHeaderIsHotUpdated() returns false once HEAP_XMAX_INVALID is set, while
the scan still tries to index the in-progress heap-only tuple and fails with:

ERROR: failed to find parent tuple for heap-only tuple at xx in table
"mxact_abort"

See the attached follow-up patch, which reproduces this. This may not be a
common case, but it looks risky to create such a race.

Best Regards,
Zhijie Hou

Attachments:

nocfbot-test-clog-abort-race.patch (application/octet-stream, 2.6K) download diff --git a/src/test/modules/injection_points/expected/multixact-aborted-updater.out b/src/test/modules/injection_points/expected/multixact-aborted-updater.out index 40750714734..abc3d0a887a 100644 --- a/src/test/modules/injection_points/expected/multixact-aborted-updater.out +++ b/src/test/modules/injection_points/expected/multixact-aborted-updater.out @@ -82,3 +82,33 @@ id|filler 2|initial (1 row) + +starting permutation: s1ddl s1abort s3brin wake +step s1ddl: + BEGIN; + INSERT INTO mxact_abort VALUES (2, 'v1'); + SAVEPOINT sp; + UPDATE mxact_abort SET filler = 'v2' WHERE id = 2; + RELEASE SAVEPOINT sp; + +step s1abort: ROLLBACK; <waiting ...> +step s3brin: + SELECT brin_desummarize_range('mxact_brin_idx', 0); + ANALYZE mxact_abort; + SELECT brin_summarize_range('mxact_brin_idx', 0); + +brin_desummarize_range +---------------------- + +(1 row) + +brin_summarize_range +-------------------- + 1 +(1 row) + +step wake: + SELECT FROM injection_points_detach('transaction-abort-after-clog'); + SELECT FROM injection_points_wakeup('transaction-abort-after-clog'); + +step s1abort: <... completed> diff --git a/src/test/modules/injection_points/specs/multixact-aborted-updater.spec b/src/test/modules/injection_points/specs/multixact-aborted-updater.spec index 98d265a2e94..f4c6fc9610a 100644 --- a/src/test/modules/injection_points/specs/multixact-aborted-updater.spec +++ b/src/test/modules/injection_points/specs/multixact-aborted-updater.spec @@ -20,6 +20,7 @@ setup CREATE EXTENSION injection_points; CREATE TABLE mxact_abort (id int PRIMARY KEY, filler text); + CREATE INDEX mxact_brin_idx ON mxact_abort USING brin (id); INSERT INTO mxact_abort VALUES (1, 'initial'); } @@ -38,6 +39,13 @@ setup { } step s1begin { BEGIN; } step s1update { UPDATE mxact_abort SET filler = 's1' WHERE id = 1; } +step s1ddl { + BEGIN; + INSERT INTO mxact_abort VALUES (2, 'v1'); + SAVEPOINT sp; + UPDATE mxact_abort SET filler = 'v2' WHERE id = 2; + RELEASE SAVEPOINT sp; +} step s1abort { ROLLBACK; } # Transaction 2 locks the row in KEY SHARE mode, to cause a multixact to be @@ -52,6 +60,11 @@ step s3update { UPDATE mxact_abort SET filler = 's3' WHERE id = 1; } step s3delete { DELETE FROM mxact_abort WHERE id = 1; } step s3keyupdate { UPDATE mxact_abort SET id = 2 WHERE id = 1; } step s3check { SELECT * FROM mxact_abort ORDER BY id; } +step s3brin { + SELECT brin_desummarize_range('mxact_brin_idx', 0); + ANALYZE mxact_abort; + SELECT brin_summarize_range('mxact_brin_idx', 0); +} session waker step wake { @@ -105,3 +118,9 @@ permutation s3keyupdate wake s3check + +permutation + s1ddl + s1abort + s3brin + wake
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 02:55 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-16 09:09   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-19 03:31     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-19 05:15       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 02:22         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-20 02:32           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Takeshi Ideriha <iderihatakeshi@gmail.com>
  2026-10-20 07:41             ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-20 07:54               ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 01:20                 ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction shihao zhong <zhong950419@gmail.com>
  2026-10-21 07:12                   ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-21 10:01                     ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
  2026-10-22 02:22                       ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Michael Paquier <michael@paquier.xyz>
  2026-10-22 20:44                         ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Manu <manuelreyesbravo@gmail.com>
  2026-10-25 08:31                           ` Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction Zhijie Hou <houzhijie22@gmail.com>
@ 2026-10-25 22:58                             ` Manu <manuelreyesbravo@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Manu @ 2026-10-25 22:58 UTC (permalink / raw)
  To: Zhijie Hou <houzhijie22@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; shihao zhong <zhong950419@gmail.com>; Takeshi Ideriha <iderihatakeshi@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi Zhijie,

On Sun, Oct 25, 2026 at 4:31 PM Zhijie Hou <houzhijie22@gmail.com>
wrote:
> This doesn't look safe to me. Even if TransactionIdDidAbort() returns true, the
> xmax may not have been removed from PGPROC yet. So this change would create the
> risky case where the old tuple has been marked as XMAX_INVAL while the new
> tuple's xmin is still considered in progress.

You're right.  On master dfd7ede529f with v2 and nocfbot-0001, your
test fails in 3 of 3 runs once nocfbot-0002 is added ("failed to find
parent tuple for heap-only tuple at (0,3)"), and passes in 4 of 4
without it.

I took the order in RecordTransactionAbort() (clog first, proc array
later) as the reason the clog alone was enough, but that order is
what opens the window: until ProcArrayEndTransaction() runs, the new
version of the tuple, whose xmin is that xmax, is still in progress,
which is the race the note at the top of heapam_visibility.c is
about.

So the clog can only be a filter there.  The attached diff replaces
nocfbot-0002: it keeps TransactionIdDidAbort(xmax) first and then
confirms with !TransactionIdIsInProgress(xmax) before setting the
hint.  It passes your test in 4 of 4 runs, shihao's spec, cases 1-3,
5 and 7-11 from measurements.txt, and make check, the isolation suite,
injection_points and test_decoding.

It keeps what made nocfbot-0002 cheap in subxact_updates.sql.  There
xmax is a released subtransaction that is still running, so it is
not aborted in the clog and the proc array is never asked.  One
VACUUM makes the same SLRU accesses as with the old nocfbot-0002:
999937 clog and 999872 subtrans, against 999873 and 1999744 with
nocfbot-0001 alone.  When xmax did abort, it pays both lookups until
the hint is set.  I have not re-timed it.

nocfbot-0001 alone (your xmin != xmax idea plus the MULTI skip) is
unchanged and passes your test in 4 of 4 runs.

Thanks for the repro.

Regards,
Manu

Attachments:

nocfbot-0002-fixed-abort-check-on-v2.diff.txt (text/plain, 1.3K) download Applies on top of v2 and nocfbot-0001. Not for the CF bot. Replaces nocfbot-0002 from 2026-10-22: the clog is only a filter, the proc array still confirms the abort before the hint is set. diff --git a/src/backend/access/heap/heapam_visibility.c b/src/backend/access/heap/heapam_visibility.c --- a/src/backend/access/heap/heapam_visibility.c +++ b/src/backend/access/heap/heapam_visibility.c @@ -1197,10 +1197,18 @@ HeapTupleSatisfiesVacuumHorizon(HeapTupl { TransactionId xmax = HeapTupleHeaderGetUpdateXid(tuple); - /* xmax == xmin: xmax is in progress too, nothing to do */ + /* + * xmax == xmin: xmax is in progress too, nothing to do. + * The clog is only a cheap filter here: a running xmax is not + * aborted there. An abort reaches the clog before the xid + * leaves the proc array, and the new version of the tuple, + * whose xmin is this xmax, is in progress until then. So the + * proc array must confirm it, as the note at the top of this + * file asks. + */ if (!TransactionIdEquals(xmax, HeapTupleHeaderGetRawXmin(tuple)) && - !TransactionIdIsInProgress(xmax) && - !TransactionIdDidCommit(xmax)) + TransactionIdDidAbort(xmax) && + !TransactionIdIsInProgress(xmax)) SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, InvalidTransactionId); }
^ permalink raw reply [nested|flat] 30+ messages in thread


end of thread, other threads:[~2026-10-27 20:03 UTC | newest]

Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-15 13:59 BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction PG Bug reporting form <noreply@postgresql.org>
2026-10-16 02:55 ` Takeshi Ideriha <iderihatakeshi@gmail.com>
2026-10-16 09:09   ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-19 03:31     ` shihao zhong <zhong950419@gmail.com>
2026-10-19 05:15       ` Takeshi Ideriha <iderihatakeshi@gmail.com>
2026-10-20 02:22         ` shihao zhong <zhong950419@gmail.com>
2026-10-20 02:32           ` Takeshi Ideriha <iderihatakeshi@gmail.com>
2026-10-20 07:41             ` Michael Paquier <michael@paquier.xyz>
2026-10-20 07:54               ` Michael Paquier <michael@paquier.xyz>
2026-10-21 01:20                 ` shihao zhong <zhong950419@gmail.com>
2026-10-21 07:12                   ` Michael Paquier <michael@paquier.xyz>
2026-10-21 10:01                     ` Zhijie Hou <houzhijie22@gmail.com>
2026-10-22 02:22                       ` Michael Paquier <michael@paquier.xyz>
2026-10-22 02:53                         ` Takeshi Ideriha <iderihatakeshi@gmail.com>
2026-10-22 03:13                           ` Michael Paquier <michael@paquier.xyz>
2026-10-22 04:55                             ` Takeshi Ideriha <iderihatakeshi@gmail.com>
2026-10-22 07:46                         ` Takeshi Ideriha <iderihatakeshi@gmail.com>
2026-10-22 08:53                         ` Zhijie Hou <houzhijie22@gmail.com>
2026-10-22 23:42                           ` Michael Paquier <michael@paquier.xyz>
2026-10-23 01:45                             ` shihao zhong <zhong950419@gmail.com>
2026-10-23 05:59                             ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-24 12:57                             ` Zhijie Hou <houzhijie22@gmail.com>
2026-10-26 00:57                               ` Michael Paquier <michael@paquier.xyz>
2026-10-26 15:41                                 ` Manu <manuelreyesbravo@gmail.com>
2026-10-26 23:34                                   ` Michael Paquier <michael@paquier.xyz>
2026-10-26 23:43                                     ` Manu <manuelreyesbravo@gmail.com>
2026-10-27 20:03                                       ` Manu <manuelreyesbravo@gmail.com>
2026-10-22 20:44                         ` Manu <manuelreyesbravo@gmail.com>
2026-10-25 08:31                           ` Zhijie Hou <houzhijie22@gmail.com>
2026-10-25 22:58                             ` Manu <manuelreyesbravo@gmail.com>

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