pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Manu <manuelreyesbravo@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Zhijie Hou <houzhijie22@gmail.com>
Cc: shihao zhong <zhong950419@gmail.com>
Cc: Takeshi Ideriha <iderihatakeshi@gmail.com>
Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Cc: alpkeles99@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
Date: Thu, 22 Oct 2026 17:44:14 -0300
Message-ID: <179270185475.2848856.14795062969027149847@gmail.com> (raw)
In-Reply-To: <atlzUChbZatun_HQ@paquier.xyz>
References: <atlzUChbZatun_HQ@paquier.xyz>

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); }

view thread (30+ messages)  latest in thread

Message-ID: <179270185475.2848856.14795062969027149847@gmail.com>
Permalink:  ../179270185475.2848856.14795062969027149847@gmail.com/
Also on:    postgresql.org/message-id/179270185475.2848856.14795062969027149847@gmail.com

 · 

reply

Reply instructions:

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

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

  To: pgsql-bugs@postgresql.org
  Cc: manuelreyesbravo@gmail.com, michael@paquier.xyz, houzhijie22@gmail.com, zhong950419@gmail.com, iderihatakeshi@gmail.com, ayushtiwari.slg01@gmail.com, alpkeles99@gmail.com
  Subject: Re: BUG #19760: CLUSTER loses a row the transaction inserted and then updated in a rolled-back subtransaction
  In-Reply-To: <179270185475.2848856.14795062969027149847@gmail.com>
  References: <atlzUChbZatun_HQ@paquier.xyz> <179270185475.2848856.14795062969027149847@gmail.com>

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

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