#!/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