#!/usr/bin/env bash
# PostgreSQL DDL 锁队列演示脚本 v1.0 — surinch.com / 盈尺
# 只在测试库上运行！脚本会新建并删除库 lock_lab，造 ROWS 行合成数据（非真实业务数据）。
# 用法：PSQL="psql -h 127.0.0.1 -U postgres" ROWS=1000000 bash pg-ddl-lock-queue-demo-v1.0.sh
set -euo pipefail
PSQL=${PSQL:-psql}
ROWS=${ROWS:-1000000}
SQL_FILE=${SQL_FILE:-$(dirname "$0")/pg-lock-blocking-tree-readonly-v1.0.sql}
t() { # label, sql  → label,秒数,ok/error,消息
  local label=$1 sql=$2 s e out rc
  s=$(date +%s.%N); out=$($PSQL -X -q -d lock_lab -tA -v ON_ERROR_STOP=1 -c "$sql" 2>&1) && rc=ok || rc=error; e=$(date +%s.%N)
  printf '%s,%.3f,%s,%s\n' "$label" "$(echo "$e - $s" | bc)" "$rc" "$(echo "$out" | tr '\n,' '  ' | cut -c1-140)"
}
echo "# version: $($PSQL -X -d postgres -tAc 'show server_version')  rows: $ROWS"
$PSQL -X -q -d postgres -c "DROP DATABASE IF EXISTS lock_lab" -c "CREATE DATABASE lock_lab"
$PSQL -X -q -d lock_lab -c "
CREATE TABLE orders (id int PRIMARY KEY, uid int NOT NULL, amount numeric(10,2) NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL);
INSERT INTO orders SELECT n, 1 + (n*7919) % 5000, round(((n*104729) % 50000)/100.0, 2), (ARRAY['paid','shipped','refund','new'])[1 + n % 4],
       '2026-01-01'::timestamptz + (n % 280) * interval '1 day' FROM generate_series(1::bigint, $ROWS) n;" -c "VACUUM ANALYZE orders"
echo "# table size: $($PSQL -X -d lock_lab -tAc "select pg_size_pretty(pg_total_relation_size('orders'))")"
echo "label,seconds,result,message"
t add_col_nullable      "ALTER TABLE orders ADD COLUMN note1 text"
t add_col_const_default "ALTER TABLE orders ADD COLUMN note2 text DEFAULT 'n/a'"
t add_col_volatile_default "ALTER TABLE orders ADD COLUMN note3 timestamptz DEFAULT clock_timestamp()"
t int_to_bigint         "ALTER TABLE orders ALTER COLUMN uid TYPE bigint"
t create_index_concurrently "CREATE INDEX CONCURRENTLY idx_orders_uid ON orders(uid)"

run_queue() { # $1 = label 前缀，$2 = ALTER 会话前置 SET（可为空）
  echo "# --- 场景 $1：会话A 改一行后不提交（idle in transaction）；会话B ALTER；会话C 普通 SELECT ---"
  ( { echo "BEGIN; UPDATE orders SET status='paid' WHERE id=1;"; sleep 12; echo "COMMIT;"; } | $PSQL -X -q -d lock_lab >/dev/null 2>&1 ) &
  local A=$!; sleep 2
  ( t "${1}_B_alter" "$2 ALTER TABLE orders ADD COLUMN q_$1 text" > "B_$1.out" ) &
  local B=$!; sleep 1
  ( t "${1}_C_select" "SELECT count(*) FROM orders WHERE id <= 10" > "C_$1.out" ) &
  local C=$!; sleep 1.5
  if [ "$1" = "no_timeout" ]; then echo "# 等待期间只读诊断 SQL 输出："; $PSQL -X -d lock_lab -P pager=off -f "$SQL_FILE"; fi
  wait $B $C; cat "B_$1.out" "C_$1.out"; wait $A || true; rm -f "B_$1.out" "C_$1.out"
}
run_queue no_timeout ""
run_queue lock_timeout_3s "SET lock_timeout = '3s';"
$PSQL -X -q -d postgres -c "DROP DATABASE lock_lab"