"""
05_xid_age_check.py —— 监控 XID 回卷剩余空间 + 长事务 + 表 freeze 年龄

依赖：
    pip install "psycopg[binary]>=3.1"

用途：放进监控脚本里定期跑，给 DBA 提供回卷预警。
"""
from __future__ import annotations

import psycopg

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"

# 安全阈值（xid age）
WARN  = 1_000_000_000   # 10 亿，warning
CRIT  = 1_500_000_000   # 15 亿，critical
LIMIT = 2_000_000_000   # 20 亿，紧急


def colorize(age: int) -> str:
    if age >= LIMIT: return f"\033[31m{age:>13,}  [EMERGENCY]\033[0m"
    if age >= CRIT:  return f"\033[31m{age:>13,}  [CRITICAL]\033[0m"
    if age >= WARN:  return f"\033[33m{age:>13,}  [WARNING]\033[0m"
    return                f"\033[32m{age:>13,}  [OK]\033[0m"


def main() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        print("=== 1) 数据库级 XID 年龄 ===")
        cur = conn.execute("""
            SELECT datname,
                   age(datfrozenxid) AS xid_age,
                   2147483648 - age(datfrozenxid) AS remaining
            FROM pg_database
            ORDER BY xid_age DESC
        """)
        print(f"  {'datname':<20} {'xid_age':>22}  {'remaining':>14}")
        for db, age, remaining in cur.fetchall():
            print(f"  {db:<20} {colorize(age)}  {remaining:>14,}")

        print("\n=== 2) Top 10 老 freeze 年龄的表 ===")
        cur = conn.execute("""
            SELECT n.nspname || '.' || c.relname AS tbl,
                   age(c.relfrozenxid) AS xid_age,
                   pg_size_pretty(pg_relation_size(c.oid)) AS size
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r','m','t')
              AND n.nspname NOT IN ('pg_catalog','information_schema')
            ORDER BY age(c.relfrozenxid) DESC
            LIMIT 10
        """)
        print(f"  {'table':<40} {'xid_age':>22}  {'size':>10}")
        for tbl, age, size in cur.fetchall():
            print(f"  {tbl:<40} {colorize(age)}  {size:>10}")

        print("\n=== 3) 当前活跃事务的最老 backend_xmin（卡 vacuum 推进的元凶） ===")
        cur = conn.execute("""
            SELECT pid, usename, state,
                   now() - xact_start AS xact_duration,
                   backend_xmin,
                   age(backend_xmin) AS xmin_age,
                   LEFT(query, 60) AS query
            FROM pg_stat_activity
            WHERE backend_xmin IS NOT NULL
            ORDER BY age(backend_xmin) DESC
            LIMIT 10
        """)
        rows = cur.fetchall()
        if not rows:
            print("  无活跃事务持有 xmin")
        else:
            print(f"  {'pid':>6} {'user':<10} {'state':<24} "
                  f"{'duration':>12} {'xmin':>10} {'xmin_age':>14}  query")
            for pid, user, state, dur, xmin, xage, q in rows:
                print(f"  {pid:>6} {user or '-':<10} {state or '-':<24} "
                      f"{str(dur):>12} {xmin:>10} {xage:>14,}  {q}")

        print("\n=== 4) 处于 idle in transaction 的连接（最毒形态） ===")
        cur = conn.execute("""
            SELECT pid, usename, application_name,
                   now() - state_change AS idle_dur,
                   LEFT(query, 80) AS last_query
            FROM pg_stat_activity
            WHERE state = 'idle in transaction'
            ORDER BY state_change
        """)
        rows = cur.fetchall()
        if not rows:
            print("  无 idle in transaction 连接 ✓")
        else:
            for pid, u, app, dur, q in rows:
                print(f"  pid={pid} user={u} app={app} idle={dur}")
                print(f"    last query: {q}")
            print("\n  💡 用 SELECT pg_terminate_backend(pid) 杀掉")

        print("\n=== 5) autovacuum 配置摘要 ===")
        cur = conn.execute("""
            SELECT name, setting
            FROM pg_settings
            WHERE name IN (
                'autovacuum',
                'autovacuum_max_workers',
                'autovacuum_naptime',
                'autovacuum_vacuum_threshold',
                'autovacuum_vacuum_scale_factor',
                'autovacuum_freeze_max_age',
                'vacuum_freeze_min_age',
                'vacuum_failsafe_age',
                'idle_in_transaction_session_timeout'
            )
            ORDER BY name
        """)
        for n, s in cur.fetchall():
            print(f"  {n:<40} = {s}")


if __name__ == "__main__":
    main()