#!/usr/bin/env bash
#
# Reproducer: stale visibility-map all-visible bit after CREATE DATABASE
# ... STRATEGY WAL_LOG + failover, leading to a wrong index-only-scan result.
#
# WHAT IT SHOWS
#   An index-only scan returns a deleted row (with "Heap Fetches: 0") on a
#   standby, while a sequential scan on the same relation correctly returns no
#   rows.  The standby's heap page has PD_ALL_VISIBLE clear but its VM page
#   still has the all-visible bit set -- a violation of the invariant that the
#   VM all-visible bit implies PD_ALL_VISIBLE.
#
# WHY
#   Two issues combine:
#     1. CREATE DATABASE ... STRATEGY WAL_LOG copies VM pages, but the
#        buffer-based copy logs them as standard pages, so the standby
#        reconstructs the VM from a hole-punched FPI and gets zeroed bits,
#        while the primary keeps the real (set) bits.  This desyncs the VM
#        between primary and standby.
#     2. After failover, a DELETE on the new primary (whose VM bit is already
#        clear) clears PD_ALL_VISIBLE but its VM clear is a no-op, so it does
#        not register the VM block in WAL.  On master/PG19, redo of that record
#        skips clearing the VM unless a VM block is registered, so the rejoined
#        standby keeps its stale all-visible bit.
#
# APPLICABILITY
#   Reproduces on PostgreSQL master and REL_19_STABLE.  On 17/18 the redo path
#   retains a fallback that clears the VM even without a registered block, so
#   the wrong-result does not occur there.
#
# PREREQUISITES
#   - PostgreSQL binaries (initdb, pg_ctl, postgres, psql, pg_basebackup) on
#     PATH, OR set PG_BINDIR to the directory containing them.
#   - The contrib extensions "pageinspect" and "pg_visibility" must be
#     installed (they ship with the standard contrib build).
#
# USAGE
#   ./repro-noop-vm-clear.sh
#   PG_BINDIR=/path/to/pg/bin ./repro-noop-vm-clear.sh
#
# The script creates a throwaway two-node cluster in a temporary directory,
# runs entirely on localhost using ephemeral ports, and cleans up on exit.
# Nothing outside its temp directory is modified.

set -euo pipefail

# --- Locate PostgreSQL binaries -------------------------------------------
if [ -n "${PG_BINDIR:-}" ]; then
	PATH="$PG_BINDIR:$PATH"
	export PATH
fi

for prog in initdb pg_ctl postgres psql pg_basebackup pg_config; do
	if ! command -v "$prog" >/dev/null 2>&1; then
		echo "error: '$prog' not found on PATH; set PG_BINDIR to your PostgreSQL bin dir" >&2
		exit 1
	fi
done

# Make shared libraries findable regardless of install layout / OS.
PG_LIBDIR="$(pg_config --libdir)"
case "$(uname -s)" in
	Darwin) export DYLD_LIBRARY_PATH="$PG_LIBDIR${DYLD_LIBRARY_PATH:+:$DYLD_LIBRARY_PATH}" ;;
	*)      export LD_LIBRARY_PATH="$PG_LIBDIR${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH}" ;;
esac

echo "Using PostgreSQL: $(postgres --version)"

# --- Configuration --------------------------------------------------------
# Pick two free localhost ports.
pick_port()
{
	# Ask the OS for an unused TCP port.
	psql -V >/dev/null 2>&1 # no-op to keep shell honest
	python3 - <<'PY' 2>/dev/null || echo 0
import socket
s = socket.socket()
s.bind(("127.0.0.1", 0))
print(s.getsockname()[1])
s.close()
PY
}

PRIMARY_PORT="${PRIMARY_PORT:-$(pick_port)}"
STANDBY_PORT="${STANDBY_PORT:-$(pick_port)}"
if [ "$PRIMARY_PORT" = 0 ] || [ "$STANDBY_PORT" = 0 ] || [ "$PRIMARY_PORT" = "$STANDBY_PORT" ]; then
	# Fallback to fixed high ports if python3 is unavailable.
	PRIMARY_PORT=${PRIMARY_PORT:-6543}
	STANDBY_PORT=6544
fi

WORK_DIR="$(mktemp -d "${TMPDIR:-/tmp}/pg-vm-repro.XXXXXX")"
REPL_USER="$(id -un)"
DB_TEMPLATE=vm_template
DB_COPY=vm_copy

cleanup()
{
	pg_ctl -D "$WORK_DIR/old_primary" stop -m immediate >/dev/null 2>&1 || true
	pg_ctl -D "$WORK_DIR/standby"     stop -m immediate >/dev/null 2>&1 || true
	rm -rf "$WORK_DIR"
}
trap cleanup EXIT

echo "Work directory: $WORK_DIR"
echo "Primary port:   $PRIMARY_PORT"
echo "Standby port:   $STANDBY_PORT"

# --- Helper: wait until a standby has replayed the primary's flushed WAL ---
wait_for_replay()
{
	local primary_port=$1
	local standby_port=$2
	local target_lsn

	target_lsn=$(psql -XAt -p "$primary_port" -d postgres \
		-c 'SELECT pg_current_wal_flush_lsn()')

	for _ in $(seq 1 300); do
		if psql -XAt -p "$standby_port" -d postgres \
			-c "SELECT pg_last_wal_replay_lsn() >= '$target_lsn'::pg_lsn" \
			| grep -qx t; then
			return
		fi
		sleep 0.1
	done

	echo "error: standby did not replay through $target_lsn" >&2
	exit 1
}

# --- Set up the initial primary -------------------------------------------
initdb -D "$WORK_DIR/old_primary" --no-sync >/dev/null
pg_ctl -D "$WORK_DIR/old_primary" -l "$WORK_DIR/old_primary.log" \
	-o "-p $PRIMARY_PORT -c listen_addresses=127.0.0.1 -c full_page_writes=off" \
	-w start >/dev/null

psql -X -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d postgres \
	-c "CREATE DATABASE $DB_TEMPLATE" >/dev/null
psql -X -q -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_TEMPLATE" <<'SQL'
CREATE EXTENSION pageinspect;
CREATE EXTENSION pg_visibility;
CREATE TABLE t(a integer);
INSERT INTO t VALUES (1);
CREATE INDEX t_a_idx ON t(a);
SQL
# Make the page all-visible/all-frozen in the VM.
psql -X -q -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_TEMPLATE" \
	-c 'VACUUM (FREEZE) t'

# --- Base backup the standby BEFORE creating the WAL_LOG database ----------
# The primary copies the template VM bytes directly (bits set), while the
# standby reconstructs the copied VM from hole-punched FPIs and gets zero bits.
pg_basebackup -h 127.0.0.1 -p "$PRIMARY_PORT" \
	-D "$WORK_DIR/standby" -R -X stream --no-sync --checkpoint=fast >/dev/null
pg_ctl -D "$WORK_DIR/standby" -l "$WORK_DIR/standby.log" \
	-o "-p $STANDBY_PORT -c listen_addresses=127.0.0.1 -c full_page_writes=off -c hot_standby=on" \
	-w start >/dev/null

psql -X -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d postgres \
	-c "CREATE DATABASE $DB_COPY TEMPLATE $DB_TEMPLATE STRATEGY WAL_LOG" >/dev/null
wait_for_replay "$PRIMARY_PORT" "$STANDBY_PORT"

echo
echo "VM all-visible after CREATE DATABASE ... STRATEGY WAL_LOG (expect divergence):"
printf '  old primary: '
psql -XAt -p "$PRIMARY_PORT" -d "$DB_COPY" \
	-c "SELECT (pg_visibility_map_summary('t')).all_visible"
printf '  standby:     '
psql -XAt -p "$STANDBY_PORT" -d "$DB_COPY" \
	-c "SELECT (pg_visibility_map_summary('t')).all_visible"

# --- Fail over: promote the standby, rejoin the old primary as a standby ---
psql -X -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d postgres \
	-c "ALTER SYSTEM SET primary_conninfo = 'host=127.0.0.1 port=$STANDBY_PORT user=$REPL_USER'" >/dev/null

pg_ctl -D "$WORK_DIR/old_primary" stop -m fast >/dev/null
pg_ctl -D "$WORK_DIR/standby" promote >/dev/null

for _ in $(seq 1 300); do
	if psql -XAt -p "$STANDBY_PORT" -d postgres \
		-c 'SELECT NOT pg_is_in_recovery()' | grep -qx t; then
		break
	fi
	sleep 0.1
done

touch "$WORK_DIR/old_primary/standby.signal"
pg_ctl -D "$WORK_DIR/old_primary" -l "$WORK_DIR/old_primary_follow.log" \
	-o "-p $PRIMARY_PORT -c listen_addresses=127.0.0.1 -c full_page_writes=off -c hot_standby=on" \
	-w start >/dev/null

# --- The triggering DELETE -------------------------------------------------
# The new primary has PD_ALL_VISIBLE set on the heap page but its VM bit is
# already clear.  DELETE clears PD_ALL_VISIBLE and its VM clear is a no-op, so
# no VM block is registered.  The rejoined old primary (now a standby) keeps
# its stale VM all-visible bit.
psql -X -v ON_ERROR_STOP=1 -p "$STANDBY_PORT" -d "$DB_COPY" \
	-c 'DELETE FROM t WHERE a = 1' >/dev/null
wait_for_replay "$STANDBY_PORT" "$PRIMARY_PORT"

echo
echo "State on the rejoined old primary (an in-recovery standby):"
psql -X -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_COPY" <<'SQL'
SELECT (pg_visibility_map_summary('t')).all_visible AS vm_all_visible,
       (flags & 4) <> 0                             AS pd_all_visible
FROM page_header(get_raw_page('t', 0));
SQL

# Force plan shapes via PGOPTIONS so no "SET" output pollutes captured values.
# enable_indexscan must stay ON for the index-only scan: disabling it also
# suppresses the index-only-scan path, and the planner falls back to a disabled
# seq scan that never consults the VM.
SEQ_OPTS="-c enable_indexscan=off -c enable_indexonlyscan=off -c enable_bitmapscan=off -c enable_seqscan=on"
IOS_OPTS="-c enable_seqscan=off -c enable_bitmapscan=off -c enable_indexscan=on -c enable_indexonlyscan=on"

echo
echo "Sequential scan plan/result (reads the heap; correct answer is 0 rows):"
PGOPTIONS="$SEQ_OPTS" psql -X -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_COPY" \
	-c 'EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM t'
SEQ=$(PGOPTIONS="$SEQ_OPTS" psql -XAt -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_COPY" \
	-c 'SELECT count(*) FROM t')
echo "  seqscan_rows = $SEQ"

echo
echo "Index-only scan plan/result (trusts the stale VM bit; note Heap Fetches):"
PGOPTIONS="$IOS_OPTS" psql -X -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_COPY" \
	-c 'EXPLAIN (ANALYZE, BUFFERS) SELECT a FROM t WHERE a = 1'
IOS=$(PGOPTIONS="$IOS_OPTS" psql -XAt -v ON_ERROR_STOP=1 -p "$PRIMARY_PORT" -d "$DB_COPY" \
	-c 'SELECT count(*) FROM (SELECT a FROM t WHERE a = 1) s')
echo "  index_only_scan_rows = $IOS"

echo
if [ "$SEQ" = 0 ] && [ "$IOS" != 0 ]; then
	echo "RESULT: BUG REPRODUCED -- index-only scan returned the deleted row"
	echo "        ($IOS) while the sequential scan correctly returned 0 rows."
	exit 0
else
	echo "RESULT: not reproduced on this build (seqscan=$SEQ, index-only=$IOS)."
	echo "        Expected seqscan=0 and index-only>0 on affected builds."
	exit 1
fi