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"
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