Input Handling & Snapshot Isolation

Handling user input is arguably the most complex technical hurdle in Tetris-SQL. Because SQL queries execute server-side within the database engine, they have zero access to the user's keyboard, stdin, or client-side events.

The Communication Bus

The logical solution is to use a database table as a middleman.

1. A Python script captures keystrokes and writes them to a table.
2. The SQL game loop reads that table on every frame to check for new commands.

We define an UNLOGGED table to act as this bus:

CREATE UNLOGGED TABLE IF NOT EXISTS Input (cmd char, ts timestamp);

(Why UNLOGGED? It prevents Postgres from writing these rapid, ephemeral updates to the Write-Ahead Log (WAL). This significantly reduces disk I/O overhead and makes the updates nearly instantaneous.)

The Snapshot Isolation Problem

If you simply add FROM Input to the recursive CTE, the game will completely ignore your keystrokes. Why?

Because of Transaction Isolation.

PostgreSQL uses Multi-Version Concurrency Control (MVCC). By default, Postgres operates in READ COMMITTED isolation mode. However, a WITH RECURSIVE statement is a single query. According to Postgres MVCC rules, a single query operates under snapshot semantics: it only sees data as it existed at the exact moment the query started.

If the Input table contains ' ' (space) when you start the game, the recursive CTE will see ' ' for the next 10,000 frames, regardless of how many times the Python script updates the table in the background. The CTE is effectively blind to concurrent writes.

Breaking Out with dblink

To break out of the snapshot isolation, we must spawn a new query scope inside the loop. We achieve this using the dblink extension.

dblink is typically used to execute queries on remote database servers. However, in Tetris-SQL, we use it as a local loopback. By executing a query via dblink, PostgreSQL spins up an entirely independent connection/session. This new session gets a brand-new snapshot of the database, allowing it to see the latest keystrokes!

First, we establish a persistent connection at the start of the game:

conn(name, _) AS (
    SELECT 'conn', dblink_connect('conn', 'dbname=' || current_database())
)

Then, inside the recursive term, we join against the dblink function:

dblink(conn.name, 'SELECT * FROM Input --' || main.frame) input (cmd char, ts timestamp)

Defeating the Query Optimizer (Memoize)

Look closely at the dblink query string above. Notice the seemingly useless SQL comment appended to the end: --' || main.frame.

This is a critical hack required to defeat the PostgreSQL Query Optimizer.

If we simply wrote dblink(conn.name, 'SELECT * FROM Input'), the PostgreSQL query planner is smart enough to realize that the function arguments are identical on every single iteration of the loop.

To optimize execution, Postgres utilizes a Memoize node. It executes the dblink query exactly once on Frame 1, caches the result in memory, and feeds the cached result to all subsequent frames. Once again, our input breaks.

By appending the dynamically changing main.frame integer as a SQL comment, the physical string passed to the dblink function changes on every iteration (e.g., SELECT * FROM Input --1, then --2).

Because the string is different, the Memoize cache misses, forcing PostgreSQL to execute a fresh Function Scan over the network socket on every single frame, successfully retrieving our real-time keystrokes.

Execution Plan Proof

If we look at the EXPLAIN output without the frame append, we see the cache hits:

->  Memoize  (loops=999)
   ... 
   Hits: 998  Misses: 1
   ->  Function Scan on dblink input (loops=1) -- only called once

With the frame append, the nested loop executes as required:

->  Nested Loop  (loops=999)
   ->  WorkTable Scan on main main_1  (loops=999)
   ->  Function Scan on dblink input  (loops=999) -- called every iteration