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: Tue, 27 Oct 2026 17:03:26 -0300
Message-ID: <179313140649.978027.15867933676406793547@gmail.com> (raw)
In-Reply-To: <179305820270.2778603.17090680794615137662@gmail.com>
References: <179305820270.2778603.17090680794615137662@gmail.com>

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"

view thread (30+ messages)

Message-ID: <179313140649.978027.15867933676406793547@gmail.com>
Permalink:  ../179313140649.978027.15867933676406793547@gmail.com/
Also on:    postgresql.org/message-id/179313140649.978027.15867933676406793547@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: <179313140649.978027.15867933676406793547@gmail.com>
  References: <179305820270.2778603.17090680794615137662@gmail.com> <179313140649.978027.15867933676406793547@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