"""
01_mvcc_basic.py —— MVCC 基础：观察 xmin / xmax / ctid 随事务的变化

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

要点：
    1. 同一行 UPDATE 多次，看 xmin 不停变化、ctid 跳到新位置
    2. 演示「同一时刻不同事务看到不同版本」
"""
from __future__ import annotations

import psycopg
from psycopg import IsolationLevel

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


def show_row(conn: psycopg.Connection, label: str) -> None:
    print(f"\n--- {label} ---")
    with conn.cursor() as cur:
        cur.execute(
            "SELECT xmin::text, xmax::text, ctid::text, id, balance "
            "FROM ch8_accounts WHERE id = 1"
        )
        row = cur.fetchone()
        print(f"  xmin={row[0]:>6}  xmax={row[1]:>6}  ctid={row[2]:>8}  "
              f"id={row[3]}  balance={row[4]}")


def reset() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("UPDATE ch8_accounts SET balance = 1000.00")
        conn.execute("VACUUM ch8_accounts")


def demo_xmin_change() -> None:
    print("=== 1) UPDATE 后 xmin 与 ctid 变化 ===")
    reset()
    with psycopg.connect(DSN, autocommit=True) as conn:
        show_row(conn, "初始状态")
        for i in range(3):
            conn.execute(
                "UPDATE ch8_accounts SET balance = balance + 1 WHERE id = 1"
            )
            show_row(conn, f"第 {i+1} 次 UPDATE 后")


def demo_snapshot_view() -> None:
    """两个 RR 事务并发，演示「快照决定看见什么」。"""
    print("\n=== 2) 双事务快照视图差异 (REPEATABLE READ) ===")
    reset()
    a = psycopg.connect(DSN); a.isolation_level = IsolationLevel.REPEATABLE_READ
    b = psycopg.connect(DSN); b.isolation_level = IsolationLevel.REPEATABLE_READ

    a.execute("SELECT 1")  # 触发 A 的 snapshot
    b.execute("SELECT 1")  # 触发 B 的 snapshot

    # A 修改 + 提交
    a.execute("UPDATE ch8_accounts SET balance = 9999 WHERE id = 1")
    a.commit()

    # B 还能看到旧值（snapshot 时 A 未提交）
    show_row(b, "B 在自己的 snapshot 中看到（应为 1000）")
    b.commit()

    # 新事务 C 看到 9999
    with psycopg.connect(DSN, autocommit=True) as c:
        show_row(c, "新事务 C 看到（应为 9999）")

    a.close(); b.close()


def demo_dead_tuple_count() -> None:
    """连续 UPDATE 后查看 n_dead_tup。"""
    print("\n=== 3) UPDATE 产生死元组的统计 ===")
    reset()
    with psycopg.connect(DSN, autocommit=True) as conn:
        for _ in range(50):
            conn.execute(
                "UPDATE ch8_accounts SET balance = balance + 1 WHERE id = 2"
            )
        # 强制刷新统计
        conn.execute("SELECT pg_stat_reset_single_table_counters("
                     "'ch8_accounts'::regclass)")
        for _ in range(50):
            conn.execute(
                "UPDATE ch8_accounts SET balance = balance + 1 WHERE id = 2"
            )

        cur = conn.execute("""
            SELECT n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd
            FROM pg_stat_user_tables
            WHERE relname = 'ch8_accounts'
        """)
        live, dead, upd, hot = cur.fetchone()
        print(f"  n_live_tup={live}  n_dead_tup={dead}  "
              f"n_tup_upd={upd}  n_tup_hot_upd={hot}")
        if upd:
            print(f"  HOT 比例 = {100*hot/upd:.1f}%")


if __name__ == "__main__":
    demo_xmin_change()
    demo_snapshot_view()
    demo_dead_tuple_count()