#!/usr/bin/env python3
"""Capture harness for DDIA Ch. 8 exercise: "Postgres has no dirty reads".

Reproduces, against a REAL PostgreSQL 16, the claim that Postgres silently
treats the SQL standard's weakest isolation level (READ UNCOMMITTED) as
READ COMMITTED -- so the dirty read the level is *named for* cannot happen.

Two sessions (A and B) run concurrently against one database. A issues an
uncommitted UPDATE; B, also asking for READ UNCOMMITTED, tries to read the
uncommitted value and instead sees the old committed one. We also print
`SHOW transaction_isolation` from inside B's READ UNCOMMITTED transaction:
on PostgreSQL 16 it echoes back `read uncommitted` (the level is accepted by
NAME), which makes the absence of the dirty read even sharper -- the label is
honored but the behavior is READ COMMITTED, because MVCC never exposes an
uncommitted row version. The proof is the read (500, not 400), not SHOW.

Deterministic and idempotent: it drops+recreates the database `ex_ch08_dirty`
every run and closes all connections before dropping. This is the maintainer's
verification path -- NOT the reader's path. The reader reproduces the same
thing by hand in two psql sessions (see dirty-read-impossible.md, Steps).

Run:
    uv run --python 3.12 --with "psycopg[binary]" python3 code/dirty_read_impossible.py
"""

import psycopg

ADMIN_DSN = "host=localhost port=5432 user=postgres dbname=study"
DB = "ex_ch08_dirty"
DSN = f"host=localhost port=5432 user=postgres dbname={DB}"


def rule():
    print("=" * 70)


def reset_database():
    """Drop and recreate ex_ch08_dirty via an autocommit admin connection."""
    with psycopg.connect(ADMIN_DSN, autocommit=True) as admin:
        admin.execute(f"DROP DATABASE IF EXISTS {DB} WITH (FORCE)")
        admin.execute(f"CREATE DATABASE {DB}")


def seed():
    """Create accounts(id, balance) and seed id=1 balance=500 (committed)."""
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("CREATE TABLE accounts (id int PRIMARY KEY, balance int)")
        conn.execute("INSERT INTO accounts VALUES (1, 500)")


def main():
    reset_database()
    seed()

    # Two independent sessions against the one database, opened by hand-style
    # so we control exactly when each statement runs and when A commits.
    a = psycopg.connect(DSN)  # session A: the writer
    b = psycopg.connect(DSN)  # session B: the reader
    a.autocommit = False
    b.autocommit = False

    rule()
    print("SETUP  accounts seeded, committed:  id=1  balance=500")
    rule()

    # --- Session A: ask for the weakest level, then update WITHOUT committing.
    a.execute("BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
    a.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    print()
    print("SESSION A (READ UNCOMMITTED, txn OPEN, NOT committed):")
    print("  BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")
    print("  UPDATE accounts SET balance = balance - 100 WHERE id = 1;")
    print("  -- UPDATE 1   (A now privately sees 400; nothing committed yet)")

    # --- Session B: also ask for READ UNCOMMITTED, then try the dirty read.
    b.execute("BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
    iso = b.execute("SHOW transaction_isolation").fetchone()[0]
    bal_pending = b.execute(
        "SELECT balance FROM accounts WHERE id = 1"
    ).fetchone()[0]
    print()
    print("SESSION B (READ UNCOMMITTED) -- read while A's UPDATE is pending:")
    print("  BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")
    print("  SHOW transaction_isolation;")
    print(f"  -- transaction_isolation = {iso}   (accepted by name!)")
    print("  SELECT balance FROM accounts WHERE id = 1;")
    print(f"  -- balance = {bal_pending}")
    if bal_pending == 500:
        print("  -- NO DIRTY READ: B sees the OLD committed 500, not A's 400,")
        print("  --   even though SHOW just confirmed 'read uncommitted'.")
    else:
        print(f"  -- UNEXPECTED: B saw {bal_pending} (a dirty read!).")
    b.rollback()  # end B's snapshot; it read nothing it needed to keep

    # --- Now A commits; B opens a fresh statement and sees the new committed value.
    a.commit()
    bal_committed = b.execute(
        "SELECT balance FROM accounts WHERE id = 1"
    ).fetchone()[0]
    print()
    print("SESSION A commits; SESSION B reads again (new statement):")
    print("  (A) COMMIT;")
    print("  (B) SELECT balance FROM accounts WHERE id = 1;")
    print(f"  -- balance = {bal_committed}   (now B sees the committed 400)")
    b.commit()

    rule()
    print("RESULT")
    print(f"  requested isolation level : read uncommitted")
    print(f"  SHOW transaction_isolation: {iso}  (accepted by name)")
    print(f"  B's read while A pending  : {bal_pending}  (dirty read would be 400)")
    print(f"  B's read after A commits  : {bal_committed}")
    verdict = (
        "no dirty read -- READ UNCOMMITTED behaves as READ COMMITTED (MVCC)"
        if bal_pending == 500
        else "UNEXPECTED -- reality diverged from the exercise's aha"
    )
    print(f"  verdict                   : {verdict}")
    rule()

    # Close all connections BEFORE dropping the database.
    a.close()
    b.close()


if __name__ == "__main__":
    main()