#!/bin/bash
# 盈尺（Surinch）PostgreSQL autovacuum / freeze / XID 实测 v1.0
# 只能在测试库上跑：会 DROP/CREATE ${DB}，改表级 autovacuum 参数，开长事务。
# 测试：PostgreSQL 17.11；自造 events 200 万行，非客户数据。
# 用法：PSQL="sudo -u postgres psql" DB=pg_freeze_lab WORK=/tmp/pgf ./pg-autovacuum-freeze-lab-v1.0.sh | tee result.txt
set -uo pipefail
PSQL=${PSQL:-"psql"}
DB=${DB:-pg_freeze_lab}
WORK=${WORK:-/tmp/pg_freeze_lab_work}
case "$DB" in *_lab) ;; *) echo "DB 名必须以 _lab 结尾"; exit 1;; esac
mkdir -p "$WORK"
ts(){ date '+%F %T.%3N'; }
now(){ date +%s.%N; }
ms(){ awk -v a="$1" -v b="$2" 'BEGIN{printf "%.0f", (b-a)*1000}'; }

echo "[$(ts)] VERSION"; $PSQL -c "SELECT version();"
$PSQL -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$DB' AND pid <> pg_backend_pid();" >/dev/null 2>&1 || true
$PSQL -c "DROP DATABASE IF EXISTS $DB;"
$PSQL -c "CREATE DATABASE $DB;"
P="$PSQL -d $DB"

echo "[$(ts)] freeze 相关参数"
$P -c "SELECT name, setting, unit FROM pg_settings WHERE name IN (
  'autovacuum','autovacuum_freeze_max_age','vacuum_freeze_min_age','vacuum_freeze_table_age',
  'vacuum_failsafe_age','autovacuum_vacuum_scale_factor','autovacuum_naptime') ORDER BY name;"

echo "[$(ts)] 造表 200 万行"
t0=$(now)
$P -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY, user_id INT NOT NULL, status SMALLINT NOT NULL,
  payload TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO events(user_id, status, payload, created_at, updated_at)
SELECT 1+(g%100000), 1+(g%4), repeat('x',40),
       timestamptz '2026-01-01' + ((g%20000000)*interval '1 second'),
       timestamptz '2026-01-01' + ((g%20000000)*interval '1 second')
FROM generate_series(1, 2000000) g;
CREATE INDEX idx_events_user ON events(user_id);
CREATE INDEX idx_events_status_updated ON events(status, updated_at);
ANALYZE events;
SELECT relname, reltuples::bigint AS est_rows,
       pg_size_pretty(pg_relation_size(oid)) AS heap,
       pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_class WHERE relname='events';
SQL
echo "RESULT|fill|ms=$(ms $t0 $(now))"

echo "[$(ts)] P0 初始指标"
$P -c "SELECT c.relname, age(c.relfrozenxid) rel_age, s.n_live_tup, s.n_dead_tup,
       age(d.datfrozenxid) db_age
FROM pg_class c JOIN pg_stat_user_tables s ON s.relid=c.oid
JOIN pg_database d ON d.datname=current_database()
WHERE c.relname='events';"

echo "[$(ts)] P2 正确顺序：先开长事务，再 UPDATE，再 VACUUM（应看到 dead but not yet removable）"
$P -c "ALTER TABLE events SET (autovacuum_enabled=off);"
cat > "$WORK/long.sql" <<'SQL'
BEGIN;
SELECT id, xmin FROM events WHERE id=1;
SELECT pg_sleep(25);
COMMIT;
SQL
$P -f "$WORK/long.sql" > "$WORK/long.out" 2>&1 &
LPID=$!
sleep 1
$P -c "SELECT pid, state, backend_xmin, NOW()-xact_start AS age FROM pg_stat_activity
       WHERE datname=current_database() AND xact_start IS NOT NULL AND pid<>pg_backend_pid();"
echo "[$(ts)] UPDATE 300000 while long TX open"
$P -c "UPDATE events SET status=status, updated_at=now() WHERE id<=300000;"
echo "[$(ts)] VACUUM while blocked"
# VACUUM 必须单独一条，不能和别的语句塞进同一个 -c
$P -c "VACUUM (VERBOSE) events;" > "$WORK/vac_blocked.out" 2>&1
rg -n "dead but not yet removable|tuples:|removed" "$WORK/vac_blocked.out" || grep -E 'dead but not yet|tuples:' "$WORK/vac_blocked.out"
$P -c "SELECT n_live_tup, n_dead_tup, ROUND(100.0*n_dead_tup/GREATEST(n_live_tup,1),2) dead_pct
       FROM pg_stat_user_tables WHERE relname='events';"
wait $LPID || true
echo "[$(ts)] VACUUM after long TX"
$P -c "VACUUM (VERBOSE) events;" > "$WORK/vac_after.out" 2>&1
grep -E 'dead but not yet|tuples:' "$WORK/vac_after.out"
$P -c "SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='events';"

echo "[$(ts)] P4 VACUUM (FREEZE)"
$P -c "SELECT 'BEFORE' mark, age(relfrozenxid) rel_age, relfrozenxid::text FROM pg_class WHERE relname='events';"
$P -c "VACUUM (FREEZE, VERBOSE) events;" > "$WORK/vac_freeze.out" 2>&1
grep -E 'frozen:|new relfrozenxid|aggressively|tuples:' "$WORK/vac_freeze.out"
$P -c "SELECT 'AFTER' mark, age(relfrozenxid) rel_age, relfrozenxid::text FROM pg_class WHERE relname='events';"

echo "[$(ts)] P5 触发 autovacuum"
$P -c "ALTER TABLE events SET (autovacuum_enabled=on, autovacuum_vacuum_scale_factor=0.02, autovacuum_vacuum_threshold=1000);"
BEFORE=$($P -At -c "SELECT autovacuum_count FROM pg_stat_user_tables WHERE relname='events';")
$P -c "UPDATE events SET status=status WHERE id<=100000;"
for i in $(seq 1 45); do
  AV=$($P -At -c "SELECT autovacuum_count FROM pg_stat_user_tables WHERE relname='events';")
  DEAD=$($P -At -c "SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname='events';")
  echo "WAIT_AV|i=$i|av=$AV|dead=$DEAD"
  if [[ "$AV" -gt "$BEFORE" ]]; then echo "AUTO VACUUM FIRED at i=$i"; break; fi
  sleep 2
done

echo "[$(ts)] P6 wraparound 余量（只读，不消耗 XID）"
$P -c "SELECT datname, age(datfrozenxid) db_age,
       (SELECT setting::bigint FROM pg_settings WHERE name='autovacuum_freeze_max_age') freeze_max_age,
       (SELECT setting::bigint FROM pg_settings WHERE name='autovacuum_freeze_max_age') - age(datfrozenxid) xid_headroom
FROM pg_database WHERE datname=current_database();"

echo "[$(ts)] P7 seq scan dirty vs clean"
$P -c "ALTER TABLE events SET (autovacuum_enabled=off);"
$P -c "UPDATE events SET status=status WHERE id<=500000;"
$P -c "SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname='events';"
$P -c "SET enable_indexscan=off; SET enable_bitmapscan=off; EXPLAIN (ANALYZE, TIMING) SELECT COUNT(*) FROM events WHERE status=1;" | tee "$WORK/scan_dirty.out"
$P -c "VACUUM events;"
$P -c "SET enable_indexscan=off; SET enable_bitmapscan=off; EXPLAIN (ANALYZE, TIMING) SELECT COUNT(*) FROM events WHERE status=1;" | tee "$WORK/scan_clean.out"
$P -c "ALTER TABLE events SET (autovacuum_enabled=on);"

echo "[$(ts)] DONE"