#!/bin/bash
# Shlok Kyal's v12 report: ALTER SUBSCRIPTION ... REFRESH PUBLICATION and
# ALTER SUBSCRIPTION ... REFRESH TABLE take the same two locks in opposite
# orders, on two different subscriptions.
#
#   REFRESH PUBLICATION : AccessShareLock on the user table
#                         -> AccessExclusiveLock on pg_subscription_rel
#   REFRESH TABLE       : RowExclusiveLock on pg_subscription_rel
#                         -> AccessExclusiveLock on the user table
#
# His recipe uses gdb to hold one session between its two locks.  This one is
# deterministic instead: REFRESH TABLE is given a second table to resolve,
# t_block, which a prepared transaction holds in AccessExclusiveLock.  The
# command therefore stops inside its first pass, after it has opened
# pg_subscription_rel and taken AccessShareLock on t1, which is the same place
# the breakpoint was.
#
# Order of events:
#   B  opens pg_subscription_rel (RowExclusiveLock), resolves t1, blocks on t_block
#   A  takes AccessShareLock on t1, then blocks on pg_subscription_rel
#   X  commits, releasing t_block
#   B  asks for AccessExclusiveLock on t1, which A holds -> cycle
#
# Usage: PGBIN=/path/to/build/bin ./poc_refresh_publication_deadlock.sh
# It builds two throwaway clusters under /tmp and removes them on exit.
# Expect "DEADLOCK" on v12-0001 and "no deadlock" on v13-0001.
set -u

PGBIN="${PGBIN:-$(pg_config --bindir 2>/dev/null)}"
if [ -z "$PGBIN" ] || [ ! -x "$PGBIN/initdb" ]; then
	echo "set PGBIN to the bin directory of the build under test" >&2
	exit 1
fi
P=/tmp/rpd_p; S=/tmp/rpd_s; PP=55951; SP=55952

cleanup() {
	"$PGBIN/psql" -p $SP -h /tmp -Xqt -d postgres -c "ROLLBACK PREPARED 'blk'" >/dev/null 2>&1
	for d in $P $S; do "$PGBIN/pg_ctl" -D $d stop -m immediate >/dev/null 2>&1; done
}
trap cleanup EXIT

for d in $P $S; do "$PGBIN/pg_ctl" -D $d stop -m immediate >/dev/null 2>&1; done
rm -rf $P $S /tmp/rpd_*.log /tmp/rpd_a.out /tmp/rpd_b.out
for d in $P $S; do "$PGBIN/initdb" -D $d --no-sync -A trust >/dev/null 2>&1; done
"$PGBIN/pg_ctl" -D $P -o "-p $PP -k /tmp -c wal_level=logical" -l /tmp/rpd_p.log start >/dev/null
"$PGBIN/pg_ctl" -D $S -o "-p $SP -k /tmp -c wal_level=logical -c max_prepared_transactions=10 -c deadlock_timeout=1s" -l /tmp/rpd_s.log start >/dev/null
sleep 2
qp(){ "$PGBIN/psql" -p $PP -h /tmp -Xqt -d postgres -c "$1" 2>&1|sed 's/^ *//;/^$/d'; }
qs(){ "$PGBIN/psql" -p $SP -h /tmp -Xqt -d postgres -c "$1" 2>&1|sed 's/^ *//;/^$/d'; }

# sub1 owns t1 and t_block; sub2 owns "obsolete" and is about to be pointed at
# t1, which is what makes its REFRESH PUBLICATION both add and remove a table.
qp "CREATE TABLE t1 (a int);
    CREATE TABLE t_block (a int);
    CREATE TABLE obsolete (a int);
    CREATE PUBLICATION pub1 FOR TABLE t1, t_block;
    CREATE PUBLICATION pub2 FOR TABLE obsolete;" >/dev/null
qs "CREATE TABLE t1 (a int);
    CREATE TABLE t_block (a int);
    CREATE TABLE obsolete (a int);" >/dev/null

conn="host=/tmp port=$PP dbname=postgres"
qs "CREATE SUBSCRIPTION sub1 CONNECTION '$conn' PUBLICATION pub1 WITH (enabled = false)" >/dev/null 2>&1
qs "CREATE SUBSCRIPTION sub2 CONNECTION '$conn' PUBLICATION pub2" >/dev/null 2>&1
for i in $(seq 1 60); do
	[ "$(qs "SELECT count(*) FROM pg_subscription_rel sr JOIN pg_subscription s ON s.oid = sr.srsubid WHERE s.subname = 'sub2' AND sr.srsubstate <> 'r'"|tr -d ' ')" = "0" ] && break; sleep 1
done
qp "ALTER PUBLICATION pub2 SET TABLE t1" >/dev/null
echo "### setup ###"
qs "SELECT s.subname, c.relname, sr.srsubstate FROM pg_subscription_rel sr
      JOIN pg_subscription s ON s.oid = sr.srsubid
      JOIN pg_class c ON c.oid = sr.srrelid ORDER BY 1, 2" | sed 's/^/  /'

# Park t_block so that REFRESH TABLE stops in its first pass.
qs "BEGIN; LOCK TABLE t_block IN ACCESS EXCLUSIVE MODE; PREPARE TRANSACTION 'blk';" >/dev/null
echo "  t_block held by a prepared transaction"

echo
echo "### B: ALTER SUBSCRIPTION sub1 REFRESH TABLE t1, t_block ###"
"$PGBIN/psql" -p $SP -h /tmp -X -d postgres -v ON_ERROR_STOP=0 \
	-c "ALTER SUBSCRIPTION sub1 REFRESH TABLE t1, t_block" > /tmp/rpd_b.out 2>&1 &
B_PID=$!
for i in $(seq 1 30); do
	[ "$(qs "SELECT count(*) FROM pg_locks WHERE relation = 't_block'::regclass AND NOT granted"|tr -d ' ')" = "1" ] && break; sleep 1
done
b_holds_cat=$(qs "SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
                   WHERE l.relation = 'pg_subscription_rel'::regclass AND l.granted
                     AND a.query LIKE '%REFRESH TABLE%'"|tr -d ' ')
b_holds_t1=$(qs "SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
                   WHERE l.relation = 't1'::regclass AND l.granted
                     AND a.query LIKE '%REFRESH TABLE%'"|tr -d ' ')
echo "  B blocked on t_block           : $(qs "SELECT count(*) FROM pg_locks WHERE relation = 't_block'::regclass AND NOT granted"|tr -d ' ')"
echo "  B holds pg_subscription_rel    : ${b_holds_cat:-0}"
echo "  B holds a lock on t1           : ${b_holds_t1:-0}"
if [ "${b_holds_cat:-0}" = "0" ]; then
	echo "  ### B is paused in the same place, but without pg_subscription_rel:"
	echo "  ### there is no lock of its for A to wait behind, so no cycle to close."
fi

echo
echo "### A: ALTER SUBSCRIPTION sub2 REFRESH PUBLICATION WITH (copy_data = false) ###"
"$PGBIN/psql" -p $SP -h /tmp -X -d postgres -v ON_ERROR_STOP=0 \
	-c "ALTER SUBSCRIPTION sub2 REFRESH PUBLICATION WITH (copy_data = false)" > /tmp/rpd_a.out 2>&1 &
A_PID=$!
for i in $(seq 1 30); do
	[ "$(qs "SELECT count(*) FROM pg_locks WHERE relation = 'pg_subscription_rel'::regclass AND NOT granted"|tr -d ' ')" = "1" ] && break; sleep 1
done
echo "  A waiting for pg_subscription_rel : $(qs "SELECT count(*) FROM pg_locks WHERE relation = 'pg_subscription_rel'::regclass AND NOT granted"|tr -d ' ')"
echo "  A holds AccessShareLock on t1     : $(qs "SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
       WHERE l.relation = 't1'::regclass AND l.granted AND l.mode = 'AccessShareLock'
         AND a.query LIKE '%REFRESH PUBLICATION%'"|tr -d ' ')"

echo
echo "### releasing t_block, which lets B ask for AccessExclusiveLock on t1 ###"
qs "COMMIT PREPARED 'blk'" >/dev/null
wait $B_PID 2>/dev/null
wait $A_PID 2>/dev/null

echo "  B said: $(grep -E 'ERROR|ALTER' /tmp/rpd_b.out | head -2 | tr '\n' ' ')"
echo "  A said: $(grep -E 'ERROR|ALTER' /tmp/rpd_a.out | head -2 | tr '\n' ' ')"

echo
if grep -q "deadlock detected" /tmp/rpd_a.out /tmp/rpd_b.out; then
	verdict="DEADLOCK"
	grep -A3 "deadlock detected" /tmp/rpd_a.out /tmp/rpd_b.out | head -6 | sed 's/^/  /'
elif grep -q "^ALTER SUBSCRIPTION" /tmp/rpd_a.out && grep -q "^ALTER SUBSCRIPTION" /tmp/rpd_b.out; then
	verdict="no deadlock, both commands succeeded"
elif grep -q "^ALTER SUBSCRIPTION" /tmp/rpd_a.out &&
     grep -q 'is also part of the subscription' /tmp/rpd_b.out; then
	# The topology this recipe builds puts t1 into sub2, so whichever command
	# commits first decides: here A does, and REFRESH TABLE then has to refuse
	# t1 because by that point it really is fed by two subscriptions.
	verdict="no deadlock, the two serialized (A committed, B refused t1 as now also sub2's)"
else
	verdict="no deadlock, but one command did not succeed -- check /tmp/rpd_[ab].out"
fi
echo "=============================================================="
echo " B held pg_subscription_rel while waiting : ${b_holds_cat:-0}"
echo " REFRESH PUBLICATION vs REFRESH TABLE     : $verdict"
echo "=============================================================="