"""Direct SQL evidence: rollback, idempotent migrations, real EXPLAIN on 12000 rows. Run inside API container: docker compose exec -T api python student-tests/database.py Synthetic SQL data is rolled back after the experiment. """ import asyncio import sys import uuid from pathlib import Path sys.path.insert(0,str(Path(__file__).resolve().parents[1])) import psycopg from db import make_pool,migrate async def main(): pool=make_pool() await pool.open() try: await pool.wait() await migrate(pool) await migrate(pool) print('PASS: migrations applied twice') async with pool.connection() as conn: before=(await (await conn.execute('SELECT count(*) n FROM chats')).fetchone())['n'] try: async with conn.transaction(): chat=uuid.uuid4() await conn.execute('INSERT INTO chats(id,title) VALUES (%s,%s)',(chat,'rollback-check')) await conn.execute('INSERT INTO members VALUES (%s,%s)',(chat,uuid.uuid4())) except psycopg.errors.ForeignKeyViolation: pass else: raise AssertionError('expected foreign key failure') after=(await (await conn.execute('SELECT count(*) n FROM chats')).fetchone())['n'] assert before==after print('PASS: rollback removes partial chat') # Roll back synthetic rows while retaining actual planner output. async with conn.transaction(force_rollback=True): user,chat=uuid.uuid4(),uuid.uuid4() await conn.execute('INSERT INTO users(id,username,display_name,password_hash) VALUES (%s,%s,%s,%s)',(user,'plan_'+uuid.uuid4().hex[:16],'plan','not-a-login-hash')) await conn.execute('INSERT INTO chats(id,title) VALUES (%s,%s)',(chat,'plan')) await conn.execute('INSERT INTO members VALUES (%s,%s)',(chat,user)) await conn.execute("INSERT INTO messages(id,chat_id,sender_id,text,created_at) SELECT gen_random_uuid(),%s,%s,'message '||n, '2026-01-01'::timestamptz+n*interval '1 microsecond' FROM generate_series(1,12000) n",(chat,user)) await conn.execute('ANALYZE messages') row=await (await conn.execute('SELECT created_at,id FROM messages WHERE chat_id=%s ORDER BY created_at,id OFFSET 9000 LIMIT 1',(chat,))).fetchone() plan=await (await conn.execute('EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id=%s AND (created_at,id)>(%s,%s) ORDER BY created_at,id LIMIT 51',(chat,row['created_at'],row['id']))).fetchall() rendered='\n'.join(r['QUERY PLAN'] for r in plan) print('EXPLAIN: 12000 messages; cursor after row 9001; page 51') print(rendered) assert 'messages_history' in rendered and 'Index' in rendered print('PASS: history uses messages_history index') finally: await pool.close() if __name__=='__main__': asyncio.run(main())