"""
02_synchronous_commit.py
------------------------
对比 synchronous_commit = on vs off 的吞吐：
  · on  ：每次 commit 都等 WAL fsync()，安全但慢
  · off ：commit 后立即返回，最多丢最后 ~600ms 事务，但不损坏库

实际差距取决于磁盘 fsync 性能：本地 SSD 可能 3 倍，云盘可能 10 倍。
"""

import time

import psycopg

CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
N = 5000


def banner(title: str) -> None:
    print("\n" + "=" * 70)
    print(f"  {title}")
    print("=" * 70)


def setup() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS ch10_sc_demo;")
        cur.execute(
            "CREATE TABLE ch10_sc_demo (id BIGSERIAL PRIMARY KEY, payload TEXT);"
        )


def run(mode: str) -> float:
    """跑 N 次单行 INSERT 单独提交，返回总耗时秒。"""
    with psycopg.connect(CONN_STR) as conn, conn.cursor() as cur:
        cur.execute(f"SET synchronous_commit = {mode};")
        t0 = time.perf_counter()
        for i in range(N):
            cur.execute(
                "INSERT INTO ch10_sc_demo (payload) VALUES (%s);", (f"row-{i}",)
            )
            conn.commit()
        return time.perf_counter() - t0


def main() -> None:
    setup()

    banner(f"对比 synchronous_commit  ({N} 次单行单独 commit)")

    print("【warm-up...】")
    run("on")  # 预热

    results = {}
    for mode in ("on", "off", "local"):
        try:
            t = run(mode)
            results[mode] = t
            tps = N / t
            print(f"  synchronous_commit = {mode:<6}  耗时 {t:6.2f}s   ≈ {tps:>8.1f} tps")
        except psycopg.Error as e:
            print(f"  synchronous_commit = {mode:<6}  失败：{e}")

    if "on" in results and "off" in results:
        print(f"\n  off 相对 on 提速 ≈ {results['on']/results['off']:.2f}x")

    banner("观察 wal_buffers / WAL Writer 状态")
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        for k in ("synchronous_commit", "fsync", "wal_buffers",
                  "wal_writer_delay", "wal_writer_flush_after",
                  "commit_delay", "commit_siblings"):
            cur.execute("SHOW %s;" % k)
            v = cur.fetchone()[0]
            print(f"  {k:<25} = {v}")

    banner("结论与建议")
    print(
        """
  · synchronous_commit = on  → 强一致；金融、订单核心必须开。
  · synchronous_commit = off → 业务能容忍丢最后 ~600ms 事务时（埋点、消息队列、
    缓存型副本、监控数据），打开能换数倍吞吐，且不会损坏数据库。
  · 单事务级别细粒度控制：BEGIN; SET LOCAL synchronous_commit = off; ...; COMMIT;
  · ⚠ 禁止把 fsync 设为 off！与 synchronous_commit 完全两码事，掉电会损坏库。
"""
    )


if __name__ == "__main__":
    main()