#!/usr/bin/env bash
# Verifies every factual claim in:
#   https://www.heatware.net/postgresql/find-last-run-auto-vacuum-and-analyze/
# Spins up its own throwaway PostgreSQL cluster, checks each claim, prints PASS/FAIL,
# and removes the cluster on exit. Nothing outside the temp datadir is touched.
#
# Usage:  ./verify-find-last-run-auto-vacuum-and-analyze.sh
# Requires: PostgreSQL 15+ client+server binaries on PATH (initdb, pg_ctl, psql).

set -uo pipefail

PASS=0; FAIL=0
ok()   { printf 'PASS  %s\n' "$1"; PASS=$((PASS+1)); }
bad()  { printf 'FAIL  %s\n' "$1"; FAIL=$((FAIL+1)); }
check(){ # check <description> <expected> <actual>
  if [ "$2" = "$3" ]; then ok "$1 (got: $3)"; else bad "$1 (expected: $2, got: $3)"; fi
}

command -v initdb >/dev/null 2>&1 || { echo "initdb not on PATH"; exit 1; }

DATADIR="$(mktemp -d "${TMPDIR:-/tmp}/pgvac.XXXXXX")"
PORT=""
cleanup() {
  [ -n "$PORT" ] && pg_ctl -D "$DATADIR" -m immediate stop >/dev/null 2>&1
  rm -rf "$DATADIR"
}
trap cleanup EXIT

# pick a free TCP port (unix sockets are avoided: mktemp paths exceed the 103-byte limit)
for p in $(seq 55910 55990); do
  if ! (exec 3<>/dev/tcp/127.0.0.1/$p) 2>/dev/null; then PORT=$p; break; fi
done
[ -n "$PORT" ] || { echo "no free port"; exit 1; }

initdb -D "$DATADIR" -U postgres --no-sync >/dev/null 2>&1 || { echo "initdb failed"; exit 1; }

PGOPTS="-p $PORT -c listen_addresses=127.0.0.1 -c unix_socket_directories=''"
# make autovacuum fire fast enough to observe inside a test run
PGOPTS="$PGOPTS -c autovacuum_naptime=1 -c autovacuum_vacuum_threshold=1 -c autovacuum_vacuum_scale_factor=0"
PGOPTS="$PGOPTS -c autovacuum_analyze_threshold=1 -c autovacuum_analyze_scale_factor=0"
start() { pg_ctl -D "$DATADIR" -o "$PGOPTS" -l "$DATADIR/server.log" start >/dev/null 2>&1; sleep 2; }
start

Q() { psql -h 127.0.0.1 -p "$PORT" -U postgres -X -Atq -c "$1" 2>&1; }

echo "== environment =="
Q "select version()"
echo

echo "== Claim 1: the article's query runs unmodified on this server =="
SQL="select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;"
if Q "$SQL" >/dev/null 2>&1 && ! Q "$SQL" | grep -qi '^ERROR'; then
  ok "documented query executes without error"
else
  bad "documented query failed: $(Q "$SQL")"
fi

echo
echo "== Claim 2: all five columns exist in pg_stat_user_tables =="
for c in relname last_vacuum last_autovacuum last_analyze last_autoanalyze schemaname; do
  n=$(Q "select count(*) from information_schema.columns where table_name='pg_stat_user_tables' and column_name='$c'")
  check "column $c exists" "1" "$n"
done

Q "create table demo(id int primary key, v text)" >/dev/null
Q "insert into demo select g, repeat('x',50) from generate_series(1,20000) g" >/dev/null

echo
echo "== Claim 3: a manual VACUUM sets last_vacuum and increments vacuum_count =="
check "last_vacuum is NULL before any vacuum" "t" "$(Q "select last_vacuum is null from pg_stat_user_tables where relname='demo'")"
Q "vacuum analyze demo" >/dev/null; sleep 1
check "last_vacuum populated after VACUUM"   "t" "$(Q "select last_vacuum is not null from pg_stat_user_tables where relname='demo'")"
check "last_analyze populated after ANALYZE" "t" "$(Q "select last_analyze is not null from pg_stat_user_tables where relname='demo'")"
check "vacuum_count incremented"             "1" "$(Q "select vacuum_count from pg_stat_user_tables where relname='demo'")"

echo
echo "== Claim 4: VACUUM FULL does NOT update last_vacuum or vacuum_count =="
BEFORE_TS=$(Q "select last_vacuum from pg_stat_user_tables where relname='demo'")
BEFORE_CT=$(Q "select vacuum_count from pg_stat_user_tables where relname='demo'")
Q "vacuum full demo" >/dev/null; sleep 2
AFTER_TS=$(Q "select last_vacuum from pg_stat_user_tables where relname='demo'")
AFTER_CT=$(Q "select vacuum_count from pg_stat_user_tables where relname='demo'")
check "last_vacuum unchanged by VACUUM FULL"  "$BEFORE_TS" "$AFTER_TS"
check "vacuum_count unchanged by VACUUM FULL" "$BEFORE_CT" "$AFTER_CT"

echo
echo "== Claim 5: autovacuum populates last_autovacuum =="
Q "update demo set v = v || 'y' where id <= 5000" >/dev/null
GOT="f"
for _ in $(seq 1 40); do
  sleep 1
  [ "$(Q "select last_autovacuum is not null or last_autoanalyze is not null from pg_stat_user_tables where relname='demo'")" = "t" ] && { GOT="t"; break; }
done
check "last_autovacuum/last_autoanalyze populated by the autovacuum daemon" "t" "$GOT"

echo
echo "== Claim 6: pg_stat_reset() erases these timestamps =="
Q "select pg_stat_reset()" >/dev/null; sleep 1
check "last_vacuum cleared by pg_stat_reset()"     "t" "$(Q "select last_vacuum is null from pg_stat_user_tables where relname='demo'")"
check "last_autovacuum cleared by pg_stat_reset()" "t" "$(Q "select last_autovacuum is null from pg_stat_user_tables where relname='demo'")"

echo
echo "== Claim 7: timestamps survive a clean restart, but not an unclean one =="
Q "vacuum demo" >/dev/null; sleep 1
pg_ctl -D "$DATADIR" -m fast restart -o "$PGOPTS" -l "$DATADIR/server.log" >/dev/null 2>&1; sleep 3
check "last_vacuum survives a clean (fast) restart" "t" "$(Q "select last_vacuum is not null from pg_stat_user_tables where relname='demo'")"
pg_ctl -D "$DATADIR" -m immediate stop >/dev/null 2>&1; sleep 2; start
check "last_vacuum discarded after an immediate/crash stop" "t" "$(Q "select last_vacuum is null from pg_stat_user_tables where relname='demo'")"

echo
echo "-------------------------------------------"
printf 'PASSED: %d   FAILED: %d\n' "$PASS" "$FAIL"
[ "$FAIL" -eq 0 ] || exit 1