Hi Shihao,
shihao zhong <[email protected]> wrote:
> The rewrite gets the error if those finish within deadlock_timeout,
> otherwise REPACK does.
That matches what I see with v3 on master (e27f3b2cad7), using the
same checks as for v2. With Thom's sequence (a transaction open for
3 s, VACUUM FULL of the TOAST relation meanwhile) and the default
deadlock_timeout of 1 s, REPACK got "deadlock detected" in all 6 runs.
With deadlock_timeout = 5s, REPACK finished and the VACUUM FULL got
the error in all 3. No update was lost in any run; master loses it
every time.
One case differs from v2: a transaction that already has an XID runs
REINDEX on the TOAST relation, with no rewrite, during the startup.
On master and with v2, REPACK completes; with v3 it gets "deadlock
detected", at both timeouts. ALTER TABLE on the table itself
deadlocks the same way on master, as you said.
repack_toast fails without 0001 and passed 20 of 20 runs with it;
check and the isolation suite pass. The script and output are
attached.
Regards,
Manu
v3 of the REPACK (CONCURRENTLY) TOAST fix, checked on master e27f3b2cad7
(--enable-cassert --enable-injection-points)
ctl3 = v3-0002 only (test without the fix), v3 = v3-0001 + v3-0002
===== v3_check.sh =====
#!/usr/bin/env bash
# The same checks as v2_check.sh, for v3 of the fix: REPACK (CONCURRENTLY)
# now locks the TOAST relation before the decoding worker starts, so a
# rewrite of it waits for REPACK. Every case runs against a build and
# reports REPACK's own outcome (ok or its error), how long it took, and the
# final value of the updated rows.
#
# New in v3_check.sh:
# xidalter a transaction that already has an XID runs ALTER TABLE on the
# table itself while the worker waits for it (v3's commit
# message says master already deadlocks here).
#
# race Thom's case without an injection point: an open transaction
# keeps the worker waiting, VACUUM FULL rewrites the TOAST
# relation meanwhile, an UPDATE of the TOASTed column commits
# right after the transaction ends. N attempts.
# hammer VACUUM FULL of the TOAST relation in a loop for SECS seconds,
# over the whole startup of REPACK (the v1 retry loop spun here).
# none the same, with no rewrite at all: the normal path.
# xidlock a transaction that already has an XID locks the TOAST
# relation (REINDEX of it) while the worker waits for it: taking
# the TOAST lock before starting the worker deadlocks here.
# xidrewrite the same, but the transaction rewrites the TOAST relation
# (CLUSTER of it) before committing.
#
# v2_check.sh <install dir> [case ...]
set -u
B=$1; shift
CASES=${*:-race hammer none xidlock xidrewrite xidalter}
N=${N:-5}
SECS=${SECS:-20}
D=${D:-$HOME/pgprog/data_v3check}
P=${P:-55711}
LOG=$HOME/pgprog/v3check.log
OUT=$HOME/pgprog/v3check-repack.out
"$B/bin/pg_ctl" -D "$D" -m immediate -w stop >/dev/null 2>&1
rm -rf "$D" "$LOG"
"$B/bin/initdb" -D "$D" -U postgres --no-sync -A trust >/dev/null 2>&1
cat >> "$D/postgresql.conf" <<EOF
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
deadlock_timeout = ${DT:-1s}
EOF
"$B/bin/pg_ctl" -D "$D" -o "-p $P" -l "$LOG" -w start >/dev/null 2>&1
q() { "$B/bin/psql" -p $P -U postgres -qtAX -c "$1" 2>&1; }
fresh() { # a new table with three TOASTed rows; sets TOAST
q "DROP TABLE IF EXISTS test" >/dev/null
q "CREATE TABLE test (id int PRIMARY KEY, big text)" >/dev/null
q "ALTER TABLE test ALTER COLUMN big SET STORAGE EXTERNAL" >/dev/null
q "INSERT INTO test SELECT g, repeat('old', 3000) FROM
generate_series(1,3) g" >/dev/null
TOAST=$(q "SELECT 'pg_toast.' || c2.relname FROM pg_class c1 JOIN
pg_class c2 ON c2.oid = c1.reltoastrelid WHERE c1.relname='test'")
}
start_repack() { # in the background; sets REPACK and T0
T0=$(date +%s.%N)
( q "REPACK (CONCURRENTLY) test" > "$OUT" 2>&1; date +%s.%N >
"$OUT.end" ) &
REPACK=$!
}
report() { # label
local limit=$((SECS + 60)) waited=0
while kill -0 $REPACK 2>/dev/null && [ $waited -lt $limit ]; do sleep
1; waited=$((waited+1)); done
wait 2>/dev/null
local secs=$(echo "$(cat "$OUT.end" 2>/dev/null || date +%s.%N) - $T0"
| bc)
local repack=$(grep -m1 -E 'ERROR|FATAL' "$OUT" | sed
's/^.*\(ERROR\|FATAL\): *//')
local value=$(q "SELECT string_agg(DISTINCT left(big, 9), ',') FROM
test")
printf ' %-12s REPACK %-50s %5.1fs value %s\n' "$1" "${repack:-ok}"
"$secs" "$value"
[ -z "$repack" ] || grep -E '^(DETAIL|HINT):' "$OUT" | sed 's/^/
/'
}
open_xact() { # seconds: a transaction with an XID, closed after the sleep
( "$B/bin/psql" -p $P -U postgres -qtAX \
-c "BEGIN" -c "SELECT pg_current_xact_id()" -c "SELECT
pg_sleep($1)" -c "COMMIT" >/dev/null 2>&1 ) &
XACT=$!
}
update_new() {
q "UPDATE test SET big = repeat('NEW', 4000) WHERE id IN (1,2,3)"
>/dev/null
}
echo "== build: $B ($("$B/bin/postgres" --version)), deadlock_timeout ${DT:-1s}"
for c in $CASES; do
case $c in
race)
for i in $(seq 1 $N); do
fresh; open_xact 3; sleep 0.5
start_repack; sleep 1
q "VACUUM FULL $TOAST" >/dev/null
wait $XACT; update_new
report "race $i"
done ;;
hammer)
fresh; open_xact 4; sleep 0.5
( until_t=$((SECONDS + SECS))
while [ $SECONDS -lt $until_t ]; do q "VACUUM FULL $TOAST"
>/dev/null; done ) &
HAMMER=$!
start_repack
wait $XACT; update_new
report "hammer ${SECS}s"
wait $HAMMER 2>/dev/null ;;
none)
for i in $(seq 1 3); do
fresh; open_xact 3; sleep 0.5
start_repack
wait $XACT; update_new
report "none $i"
done ;;
xidlock|xidrewrite|xidalter)
fresh
# LOCK TABLE is refused on a TOAST relation, so use commands
that
# lock it for real: REINDEX takes ShareLock on it without a
rewrite,
# CLUSTER rewrites it (new relfilenumber). xidalter locks the
table
# itself instead.
if [ $c = xidlock ]; then
stmt="REINDEX TABLE $TOAST"
elif [ $c = xidalter ]; then
stmt="ALTER TABLE test ADD COLUMN extra int"
else
idx=$(q "SELECT c.relname FROM pg_index i JOIN pg_class
c ON c.oid = i.indexrelid WHERE i.indrelid = '$TOAST'::regclass")
stmt="CLUSTER $TOAST USING $idx"
fi
( "$B/bin/psql" -p $P -U postgres -qtAX \
-c "BEGIN" -c "INSERT INTO test VALUES (100, 'x')" -c
"SELECT pg_sleep(1.5)" \
-c "$stmt" -c "SELECT pg_sleep(1)" -c "COMMIT" >
"$OUT.s1" 2>&1 ) &
S1=$!
sleep 0.5; start_repack
wait $S1
s1=$(grep -m1 -E 'ERROR' "$OUT.s1" | sed 's/^.*ERROR: *//')
update_new
report "$c"
echo " session 1 ($stmt): ${s1:-ok}" ;;
esac
done
grep -E 'deadlock detected' "$LOG" | head -3 | sed 's/^/ log: /'
"$B/bin/pg_ctl" -D "$D" -m immediate -w stop >/dev/null 2>&1
===== v3_all.sh =====
#!/bin/bash
# Everything for the v3 review, in one run:
# 1. behaviour checks (v3_check.sh) on the control build (test only) and on v3
# 2. the injection_points tests of both: repack_toast should fail without the
fix
# 3. repack_toast alone N times on v3 (it relies on locks and lock_timeout)
# 4. core regression + isolation on v3
set -u
A=$(cd "$(dirname "$0")" && pwd)
RUNS=${RUNS:-20}
for b in ctl3 v3; do
bash $A/v3_check.sh $HOME/pgtoast-i-$b
echo
done
for b in ctl3 v3; do
echo "== injection_points check, $b"
make -C $HOME/pgtoast-$b/src/test/modules/injection_points check 2>&1 \
| grep -E '^(ok|not ok|# All|# [0-9]+ of)|tests (passed|failed)' | tail -30
done
echo "== repack_toast alone, $RUNS runs, v3"
pass=0; fail=0
for i in $(seq $RUNS); do
if make -C $HOME/pgtoast-v3/src/test/modules/injection_points check \
REGRESS= ISOLATION=repack_toast > /tmp/claude-1000/rt_$i.log 2>&1; then
pass=$((pass + 1))
else
fail=$((fail + 1)); cp /tmp/claude-1000/rt_$i.log
$A/v3_repack_toast_fail_$i.log
fi
done
echo "repack_toast: $pass passed, $fail failed of $RUNS"
echo "== core regression + isolation, v3"
make -C $HOME/pgtoast-v3 -s check 2>&1 | grep -E '# All|# [0-9]+ of|not ok' |
tail -5
make -C $HOME/pgtoast-v3/src/test/isolation -s check 2>&1 | grep -E '# All|#
[0-9]+ of|not ok' | tail -5
echo V3-ALL-DONE
===== ./v3_all.sh (deadlock_timeout 1s) =====
== build: /home/manu/pgtoast-i-ctl3 (postgres (PostgreSQL) 20devel)
race 1 REPACK ok 2.5s
value oldoldold
race 2 REPACK ok 2.5s
value oldoldold
race 3 REPACK ok 2.7s
value oldoldold
race 4 REPACK ok 2.5s
value oldoldold
race 5 REPACK ok 2.5s
value oldoldold
hammer 20s REPACK ok 3.5s
value oldoldold
none 1 REPACK ok 2.5s
value NEWNEWNEW
none 2 REPACK ok 2.5s
value NEWNEWNEW
none 3 REPACK ok 2.5s
value NEWNEWNEW
xidlock REPACK ok 2.0s
value NEWNEWNEW,x
session 1 (REINDEX TABLE pg_toast.pg_toast_23013): ok
xidrewrite REPACK ok 2.0s
value oldoldold,x
session 1 (CLUSTER pg_toast.pg_toast_23041 USING
pg_toast_23041_index): ok
xidalter REPACK deadlock detected 1.0s
value NEWNEWNEW,x
DETAIL: Process 433771 waits for ShareLock on transaction 2346;
blocked by process 433611.
HINT: See server log for query details.
session 1 (ALTER TABLE test ADD COLUMN extra int): ok
log: 2026-09-25 03:04:28.970 -03 [433771] ERROR: deadlock detected
log: 2026-09-25 03:04:28.973 -03 [433754] ERROR: deadlock detected
== build: /home/manu/pgtoast-i-v3 (postgres (PostgreSQL) 20devel)
race 1 REPACK deadlock detected 3.7s
value NEWNEWNEW
DETAIL: Process 434893 waits for ShareLock on transaction 668;
blocked by process 435170.
HINT: See server log for query details.
race 2 REPACK deadlock detected 3.7s
value NEWNEWNEW
DETAIL: Process 436301 waits for ShareLock on transaction 675;
blocked by process 436572.
HINT: See server log for query details.
race 3 REPACK deadlock detected 3.6s
value NEWNEWNEW
DETAIL: Process 437506 waits for ShareLock on transaction 682;
blocked by process 437794.
HINT: See server log for query details.
race 4 REPACK deadlock detected 3.7s
value NEWNEWNEW
DETAIL: Process 438838 waits for ShareLock on transaction 689;
blocked by process 439118.
HINT: See server log for query details.
race 5 REPACK deadlock detected 3.6s
value NEWNEWNEW
DETAIL: Process 440083 waits for ShareLock on transaction 696;
blocked by process 440416.
HINT: See server log for query details.
hammer 20s REPACK deadlock detected 4.5s
value NEWNEWNEW
DETAIL: Process 441414 waits for ShareLock on transaction 704;
blocked by process 441416.
HINT: See server log for query details.
none 1 REPACK ok 2.5s
value NEWNEWNEW
none 2 REPACK ok 2.5s
value NEWNEWNEW
none 3 REPACK ok 2.5s
value NEWNEWNEW
xidlock REPACK deadlock detected 1.0s
value NEWNEWNEW,x
DETAIL: Process 454261 waits for ShareLock on transaction 2307;
blocked by process 454120.
HINT: See server log for query details.
session 1 (REINDEX TABLE pg_toast.pg_toast_22881): ok
xidrewrite REPACK deadlock detected 1.0s
value NEWNEWNEW,x
DETAIL: Process 455050 waits for ShareLock on transaction 2313;
blocked by process 454885.
HINT: See server log for query details.
session 1 (CLUSTER pg_toast.pg_toast_22896 USING
pg_toast_22896_index): ok
xidalter REPACK deadlock detected 1.0s
value NEWNEWNEW,x
DETAIL: Process 455852 waits for ShareLock on transaction 2319;
blocked by process 455689.
HINT: See server log for query details.
session 1 (ALTER TABLE test ADD COLUMN extra int): ok
log: 2026-09-25 03:04:34.927 -03 [434893] ERROR: deadlock detected
log: 2026-09-25 03:04:35.076 -03 [434892] ERROR: deadlock detected
log: 2026-09-25 03:04:39.335 -03 [436301] ERROR: deadlock detected
== injection_points check, ctl3
ok 1 - injection_points 72 ms
ok 2 - hashagg 17 ms
ok 3 - reindex_conc 16 ms
ok 4 - vacuum 15 ms
# All 4 tests passed.
ok 1 - basic 81 ms
ok 2 - inplace 837 ms
ok 3 - reindex_concurrently_deferred 111 ms
ok 4 - repack 181 ms
ok 5 - repack_commit_race 511 ms
ok 6 - repack_decode 267 ms
ok 7 - repack_temporal 151 ms
ok 8 - repack_temporal_multirange 156 ms
not ok 9 - repack_toast 537 ms
ok 10 - ri_fastpath_reindex 341 ms
ok 11 - ri_fastpath_snapshot 67 ms
ok 12 - syscache-update-pruned 658 ms
ok 13 - wait_cleanup 79 ms
ok 14 - heap_lock_update 134 ms
ok 15 - on_conflict_probe_window 283 ms
# 1 of 15 tests failed.
== injection_points check, v3
ok 1 - injection_points 73 ms
ok 2 - hashagg 15 ms
ok 3 - reindex_conc 18 ms
ok 4 - vacuum 20 ms
# All 4 tests passed.
ok 1 - basic 91 ms
ok 2 - inplace 817 ms
ok 3 - reindex_concurrently_deferred 112 ms
ok 4 - repack 180 ms
ok 5 - repack_commit_race 497 ms
ok 6 - repack_decode 249 ms
ok 7 - repack_temporal 141 ms
ok 8 - repack_temporal_multirange 142 ms
ok 9 - repack_toast 521 ms
ok 10 - ri_fastpath_reindex 343 ms
ok 11 - ri_fastpath_snapshot 62 ms
ok 12 - syscache-update-pruned 563 ms
ok 13 - wait_cleanup 69 ms
ok 14 - heap_lock_update 126 ms
ok 15 - on_conflict_probe_window 288 ms
# All 15 tests passed.
== repack_toast alone, 20 runs, v3
repack_toast: 20 passed, 0 failed of 20
== core regression + isolation, v3
# All 239 tests passed.
# All 133 tests passed.
V3-ALL-DONE
===== DT=5s N=3 ./v3_check.sh <v3> race xidlock xidalter =====
== build: /home/manu/pgtoast-i-v3 (postgres (PostgreSQL) 20devel),
deadlock_timeout 5s
race 1 REPACK ok 6.0s
value NEWNEWNEW
race 2 REPACK ok 6.0s
value NEWNEWNEW
race 3 REPACK ok 6.0s
value NEWNEWNEW
xidlock REPACK deadlock detected 5.0s
value NEWNEWNEW,x
DETAIL: Process 531395 waits for ShareLock on transaction 691;
blocked by process 531224.
HINT: See server log for query details.
session 1 (REINDEX TABLE pg_toast.pg_toast_16465): ok
xidalter REPACK deadlock detected 5.0s
value NEWNEWNEW,x
DETAIL: Process 533699 waits for ShareLock on transaction 697;
blocked by process 533548.
HINT: See server log for query details.
session 1 (ALTER TABLE test ADD COLUMN extra int): ok
log: 2026-09-25 03:08:05.832 -03 [523132] ERROR: deadlock detected
log: 2026-09-25 03:08:13.400 -03 [525785] ERROR: deadlock detected
log: 2026-09-25 03:08:20.968 -03 [528631] ERROR: deadlock detected
log excerpt, first deadlock with deadlock_timeout 5s:
2026-09-25 03:08:05.832 -03 [523132] ERROR: deadlock detected
2026-09-25 03:08:05.832 -03 [523132] DETAIL: Process 523132 waits for
AccessExclusiveLock on relation 16388 of database 5; blocked by process 522701.
Process 522701 waits for ShareLock on transaction 668; blocked by
process 523132.
Process 523132: VACUUM FULL pg_toast.pg_toast_16384
Process 522701: <command string not enabled>
2026-09-25 03:08:05.832 -03 [523132] HINT: See server log for query details.
2026-09-25 03:08:05.832 -03 [523132] STATEMENT: VACUUM FULL
pg_toast.pg_toast_16384
