Course
ai-dev-tools-zoomcamp
Question
Why does swapping SQLite for PostgreSQL in Agent Relay (Homework 3, Question 4) break every authenticated request with a syntax error?
Answer
The starter's database.py has a context manager, immediate_transaction(), that opens every transaction with a raw connection.exec_driver_sql("BEGIN IMMEDIATE"). BEGIN IMMEDIATE is SQLite-specific syntax — it reserves SQLite's writer lock up front — and it's invalid on PostgreSQL.
This isn't a minor edge case: immediate_transaction() wraps authenticate(), create_task(), claim_one(), heartbeat(), and commit_terminal(). Once RELAY_DATABASE_URL points at Postgres, essentially every authenticated endpoint fails immediately with a syntax error, even though the rest of the codebase (SQLAlchemy models, psycopg as a dependency, no other raw SQL) is already database-agnostic by design, per SPEC.md.
Fix: only issue BEGIN IMMEDIATE when the backend is actually SQLite; let PostgreSQL use SQLAlchemy's normal autobegin transaction instead.
def _is_sqlite(database_url: str) -> bool:
return database_url.startswith("sqlite")
@contextmanager
def immediate_transaction():
connection = engine.connect()
session = Session(bind=connection, expire_on_commit=False, autoflush=True)
try:
if _is_sqlite(DATABASE_URL):
connection.exec_driver_sql("BEGIN IMMEDIATE")
yield session
session.commit()
finally:
session.close()
connection.close()
SQLite behavior is unchanged — it still gets the writer-lock reservation. One thing to know going in: on SQLite, BEGIN IMMEDIATE's writer lock was doing double duty — it also happens to be why concurrent task claims never overlap. Postgres's default autobegin has no equivalent, so this fix alone doesn't restore that guarantee; it needs SELECT ... FOR UPDATE SKIP LOCKED on the claim query for full parity, which is exactly the seam SPEC.md itself flags for a future PostgreSQL port. Worth knowing if your test suite includes a concurrent-claims test — it may need to be skipped against Postgres until that follow-up hardening is in place.
Checklist
Course
ai-dev-tools-zoomcamp
Question
Why does swapping SQLite for PostgreSQL in Agent Relay (Homework 3, Question 4) break every authenticated request with a syntax error?
Answer
The starter's database.py has a context manager, immediate_transaction(), that opens every transaction with a raw connection.exec_driver_sql("BEGIN IMMEDIATE"). BEGIN IMMEDIATE is SQLite-specific syntax — it reserves SQLite's writer lock up front — and it's invalid on PostgreSQL.
This isn't a minor edge case: immediate_transaction() wraps authenticate(), create_task(), claim_one(), heartbeat(), and commit_terminal(). Once RELAY_DATABASE_URL points at Postgres, essentially every authenticated endpoint fails immediately with a syntax error, even though the rest of the codebase (SQLAlchemy models, psycopg as a dependency, no other raw SQL) is already database-agnostic by design, per SPEC.md.
Fix: only issue BEGIN IMMEDIATE when the backend is actually SQLite; let PostgreSQL use SQLAlchemy's normal autobegin transaction instead.
def _is_sqlite(database_url: str) -> bool:
return database_url.startswith("sqlite")
@contextmanager
def immediate_transaction():
connection = engine.connect()
session = Session(bind=connection, expire_on_commit=False, autoflush=True)
try:
if _is_sqlite(DATABASE_URL):
connection.exec_driver_sql("BEGIN IMMEDIATE")
yield session
session.commit()
finally:
session.close()
connection.close()
SQLite behavior is unchanged — it still gets the writer-lock reservation. One thing to know going in: on SQLite, BEGIN IMMEDIATE's writer lock was doing double duty — it also happens to be why concurrent task claims never overlap. Postgres's default autobegin has no equivalent, so this fix alone doesn't restore that guarantee; it needs SELECT ... FOR UPDATE SKIP LOCKED on the claim query for full parity, which is exactly the seam SPEC.md itself flags for a future PostgreSQL port. Worth knowing if your test suite includes a concurrent-claims test — it may need to be skipped against Postgres until that follow-up hardening is in place.
Checklist