#!/bin/bash
# WAIT FOR ... MODE standby_write/standby_flush deadlocks a standby in ARCHIVE
# RECOVERY.  No walreceiver exists, so write/flush have no independent progress:
#   vanilla  writtenUpto/flushedUpto stay 0 -> the wait is UNSATISFIABLE.
#   floored  Max(walrcv, replay) collapses to the replay position, so the
#            write/flush waiter carries the same B -> S edge as standby_replay.
set -u
DELAY=${1:--1}                          # max_standby_archive_delay
MODE=${2:-standby_flush}
INST=${3:-/Users/qqj/Downloads/postgres-master/inst}
B=/tmp/archive_repro; S=/tmp/archive_sock; A=/tmp/arch
rm -rf $B $S $A; mkdir -p $B $S $A
trap 'for d in p s; do $INST/bin/pg_ctl -D $B/$d stop -m immediate; done >/dev/null 2>&1; rm -rf $S' EXIT

pri() { $INST/bin/psql -X -h $S -p 5501 -d postgres -At -F' | ' -c "$1"; }
sby() { $INST/bin/psql -X -h $S -p 5502 -d postgres -At -F' | ' -c "$1"; }
waiters() { sby "SELECT pid, backend_type, wait_event_type, wait_event, left(query,40)
                 FROM pg_stat_activity
                 WHERE backend_type = 'startup'
                       OR wait_event LIKE 'WaitForWal%'
                       OR wait_event_type = 'Lock'"; }

$INST/bin/initdb -D $B/p -A trust --no-sync >/dev/null
cat >> $B/p/postgresql.conf <<EOF
port=5501
unix_socket_directories='$S'
listen_addresses=''
archive_mode=on
archive_command='cp %p $A/%f'
EOF
$INST/bin/pg_ctl -D $B/p -l $B/p.log -w start >/dev/null
pri "CREATE TABLE t(i int)" >/dev/null

# Base backup WITHOUT -R: no primary_conninfo, so no walreceiver.
# standby.signal + restore_command = pure archive recovery.
$INST/bin/pg_basebackup -h $S -p 5501 -D $B/s -c fast --no-sync >/dev/null
touch $B/s/standby.signal
cat >> $B/s/postgresql.conf <<EOF
port=5502
unix_socket_directories='$S'
listen_addresses=''
restore_command='cp $A/%f %p'
max_standby_archive_delay=$DELAY
deadlock_timeout=1s
log_recovery_conflict_waits=on
EOF
$INST/bin/pg_ctl -D $B/s -l $B/s.log -w start >/dev/null

mkfifo $B/b.fifo
$INST/bin/psql -X -h $S -p 5502 -d postgres > $B/b.out 2>&1 < $B/b.fifo &
exec 3> $B/b.fifo

echo "BEGIN; SELECT count(*) FROM t;" >&3      # B holds AccessShareLock(t)
sleep 1

pri "DROP TABLE t" >/dev/null
L=$(pri "SELECT pg_current_wal_insert_lsn()")
pri "SELECT pg_switch_wal()" >/dev/null        # force the segment into the archive
sleep 4

echo "== no walreceiver in archive recovery:"
echo "   pg_last_wal_receive_lsn = [$(sby 'SELECT pg_last_wal_receive_lsn()')]"
echo "   walreceiver procs       = $(sby "SELECT count(*) FROM pg_stat_activity WHERE backend_type='walreceiver'")"
echo "   archiver procs          = $(sby "SELECT count(*) FROM pg_stat_activity WHERE backend_type='archiver'")"
echo "== startup is already stalled on the lock:"; waiters

echo "WAIT FOR LSN '$L' WITH (MODE '$MODE');" >&3
sleep 10

echo "== after 10s, MODE=$MODE, max_standby_archive_delay = $DELAY"
echo "target             $L"
echo "replay stuck at    $(sby 'SELECT pg_last_wal_replay_lsn()')"
waiters
echo "B session          $(tr '\n' ' ' < $B/b.out)"
echo "deadlock detected  $(grep -c 'deadlock detected' $B/s.log)"
grep -h 'still waiting\|Conflicting\|canceling' $B/s.log | tail -3