On Tue, 8 Apr 2025 at 09:48, Tom Lane <[email protected]> wrote: > > Andres Freund <[email protected]> writes: > > On 2025-04-08 00:11:55 -0400, Tom Lane wrote: > >> This feels quite adjacent to my complaint here: > >> https://www.postgresql.org/message-id/2045026.1743801143%40sss.pgh.pa.us > >> though perhaps it's not exactly the same. > > > That does sound rather plausible. What an odd coincidence that it failed > > like > > that so close to your email. While this specific failure probably couldn't > > have happened much earlier, it seems that it could have as part of > > pg_upgrade > > for longer. > > I think pg_upgrade is not vulnerable to the problem, or at least not > the identical problem, because it doesn't expect pg_restore to load > table data. So I think we didn't previously have any test cases > that would expose this :-(. What I find surprising is that we > didn't get field reports much sooner. > > regards, tom lane > >
I was able to reproduce this failure reliably on HEAD e5d25959cf87 Attached sh script (AI generated) fails 6-7 times per 100 iteration run for me. The root cause is catalog check race and it is not pg_restore-only. The error is real and server-side. The referenced partitioned PK index is left permanently indisvalid=false by a race between the last two concurrent ATTACH statements, and nothing ever retries the validation afterwards. So, I managed to create an isolation test with 2-level partitioning which hits this issue in the (i believe) same way as shell reproducer does. The issue does not reproduce with one-level partitioning. The key component here is that first level LEAF partitions are created invalid. So, when two last ATTACH statements validate top-level index validity inside ATExecAttachPartitionIdx -> validatePartitionedIndex they count the number of other valid indexes. With some execution ordering we can get into a schedule where 2 & 3 partition indexes are about-to-be valid (but this state is not yet committed), and they concurrently recount that there are 2 out of 3 valid indexes, then each of this statement commits. So, each statement runs before the other's transaction commits, so no snapshot taken by either can ever observe the complete set. I didn't came up with fix idea yet. -- Best regards, Kirill Reshke
#!/bin/sh
# ============================================================================
# Reproducer for the buildfarm/wiki bug 1.25 ( PostgreSQL ):
# "002_pg_upgrade.pl failed due to primary key not restored"
# pg_upstream thread ("pg_dump/restore failure (dependency?) on BF serinus"):
# https://www.postgresql.org/message-id/l4joqljvbou26unhplkdj7m5olxn7rsy2loqlcta44nie5k3bs@zudmva7han7g
#
# Symptom:
# pg_restore: error: could not execute query: ERROR: there is no unique
# constraint matching given keys for referenced table "pk"
# Command was: ALTER TABLE fkpart5.fk
# ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES fkpart5.pk(a);
#
# Mechanism (as observed on PostgreSQL master, --enable-cassert, Linux x86_64):
# - schema with partitioned PK/FK (fkpart0..fkpart6 from foreign_key.sql)
# - pg_dump -Fc, then PARALLEL pg_restore into a fresh database
# - only fails with -j > 1 (observed at j=8/16/32; occurs with j=1)
# - all TOC dependencies of the failing FK are satisfied and committed
# before it starts; the post-failure schema still contains all PKs,
# i.e. the error comes from the server-side check (transformFkeyCheckAttrs,
# tablecmds.c, RelationGetIndexList + indisvalid scan) seeing a stale view
# of the referenced partitioned table while other restore workers run
# concurrent DDL. The second error of "errors ignored on restore: 2" is
# lost due to interleaved worker stderr writes.
#
# Reproduction quality (measured on 8-core VM, NVMe):
# 10 copies of the fkpart0..6 section, pg_restore -j32: ~10% of iterations
#
# Usage:
# ./fk_no_unique_repro.sh # run with defaults (60 iterations, j=32)
#
# Tunables (environment):
# REPRO_ITERATIONS number of restore iterations (default: 60)
# REPRO_JOBS pg_restore parallel jobs (default: 32)
# REPRO_COPIES how many copies of the fkpart schema (default: 10)
# REPRO_FKSQL path to foreign_key.sql (default: guess)
# PGHOST/PGPORT/PGUSER/PGDATABASE connection for pg_dump/pg_restore/psql
#
# Exit code: 0 = not reproduced this run, 1 = reproduced, 2 = setup error.
# Fail artifacts are saved under ./repro_results/
# ============================================================================
set -u
ITERATIONS=${REPRO_ITERATIONS:-60}
JOBS=${REPRO_JOBS:-32}
COPIES=${REPRO_COPIES:-10}
PGHOST=${PGHOST:-127.0.0.1}
PGPORT=${PGPORT:-55432}
PGUSER=${PGUSER:-postgres}
RESULTS=$(pwd)/repro_results
mkdir -p "$RESULTS"
WORK=$(mktemp -d) || exit 2
trap 'rm -rf "$WORK"' EXIT
# --- locate foreign_key.sql -------------------------------------------------
FKSQL=${REPRO_FKSQL:-}
if [ -z "$FKSQL" ]; then
for cand in \
"$(pwd)/src/test/regress/sql/foreign_key.sql" \
"$HOME/postgres-dev/src/test/regress/sql/foreign_key.sql" \
"/usr/local/pgsql/src/test/regress/sql/foreign_key.sql"
do
[ -f "$cand" ] && FKSQL=$cand && break
done
fi
[ -n "$FKSQL" ] && [ -f "$FKSQL" ] || {
echo "cannot find foreign_key.sql; set REPRO_FKSQL" >&2; exit 2; }
echo "== using fk sql: $FKSQL"
CONN="-h $PGHOST -p $PGPORT -U $PGUSER"
if ! psql $CONN -d postgres -X -c 'SELECT 1' >/dev/null 2>&1; then
echo "cannot connect to postgres at $PGHOST:$PGPORT as $PGUSER" >&2; exit 2
fi
SRCDB=fkrepro_src
DSTDB=fkrepro_dst
# --- build amplified schema (COPIES rewritten namespaces) --------------------
sed -n '/^create schema fkpart0/,$p' "$FKSQL" | grep -v '^\\d' > "$WORK/fk_base.sql"
[ -s "$WORK/fk_base.sql" ] || { echo "fkpart section not found in $FKSQL" >&2; exit 2; }
: > "$WORK/fk_big.sql"
i=0
while [ "$i" -lt "$COPIES" ]; do
sed "s/fkpart0/fkp${i}_0/g; s/fkpart1/fkp${i}_1/g; s/fkpart2/fkp${i}_2/g;
s/fkpart3/fkp${i}_3/g; s/fkpart4/fkp${i}_4/g; s/fkpart5/fkp${i}_5/g;
s/fkpart6/fkp${i}_6/g" "$WORK/fk_base.sql" >> "$WORK/fk_big.sql"
i=$((i + 1))
done
echo "== schema: $COPIES copies of fkpart0..6"
# --- load source database and take the dump ---------------------------------
dropdb $CONN --if-exists $SRCDB 2>/dev/null
createdb $CONN $SRCDB || exit 2
# (no ON_ERROR_STOP here on purpose: the cut section contains statements
# expected to fail, they are negative-test material from foreign_key.sql)
psql $CONN -X -q -d $SRCDB -f "$WORK/fk_big.sql" >"$WORK/load.log" 2>&1
pg_dump $CONN -Fc -d $SRCDB -f "$RESULTS/fkrepro.dump" || exit 2
dropdb $CONN --if-exists $SRCDB 2>/dev/null
echo "== dump ready: $RESULTS/fkrepro.dump"
# --- main loop ---------------------------------------------------------------
fails=0
it=1
while [ "$it" -le "$ITERATIONS" ]; do
dropdb $CONN --if-exists $DSTDB 2>/dev/null
createdb $CONN $DSTDB || exit 2
if ! pg_restore $CONN -j "$JOBS" -d $DSTDB "$RESULTS/fkrepro.dump" \
> "$WORK/restore_$it.log" 2>&1; then
target=no
grep -q 'no unique constraint matching given keys' "$WORK/restore_$it.log" \
&& target=yes
fails=$((fails + 1))
cp "$WORK/restore_$it.log" "$RESULTS/fail_${it}.log"
if [ "$target" = yes ]; then
# keep post-state schema of the failed restore for forensics
pg_dump $CONN --schema-only -d $DSTDB \
-f "$RESULTS/fail_${it}_poststate.sql" 2>/dev/null
echo "iter $it: REPRODUCED (target bug), artifacts saved"
else
echo "iter $it: restore failed with a DIFFERENT error (see $RESULTS/fail_${it}.log)"
grep -m2 'error:' "$WORK/restore_$it.log" | sed 's/^/ /'
fi
else
echo "iter $it: ok"
fi
dropdb $CONN --if-exists $DSTDB 2>/dev/null
it=$((it + 1))
done
echo "== done: $fails failures out of $ITERATIONS iterations (jobs=$JOBS, copies=$COPIES)"
if [ "$fails" -gt 0 ]; then
echo "== artifacts in $RESULTS"
exit 1
fi
exit 0
v1-0001-Add-test-for-concurrent-partitioned-index-attach-.patch
Description: Binary data
