Puzzleshooting

Puzzleshot #025 - Mortal Duckbat!

raw data→duckdb→quack→duckdb→result

Two processes. One database file. Fight!

Twenty-five puzzles in, so something new this time: DuckDB talking to other DuckDBs.

Back in #005 we said DuckDB needs no server — with a footnote that something called Quack was changing that. Time to find out what that footnote was about.

First, the setup. In #023 and #024 we counted requests per minute from a log, once with awk on the live stream and once with DuckDB on the finished file. This time we put a database in the middle: awk watches the stream and writes one row per minute into a DuckDB database, and a dashboard asks that database questions.

The writer:

tail -n +1 -F access.log | awk '
BEGIN {
  print "CREATE TABLE IF NOT EXISTS per_minute (minute TIMESTAMP, requests INTEGER, errors INTEGER);"
  fflush()
}
{
  m = $1 " " substr($2, 1, 5) ":00"
  if (m != cur) {
    if (cur != "") {
      printf "INSERT INTO per_minute VALUES (TIMESTAMP \047%s\047, %d, %d);\n", cur, total, errs
      fflush()
    }
    cur = m; total = 0; errs = 0
  }
  total++
  if ($5 >= 500) errs++
}' | duckdb log.db

The log format is the same as before (date, time, method, path, status).

The challenge:

Round 1:

Start the writer. While it’s running, open a second terminal and try to read the table with: duckdb -readonly log.db -c “SELECT * FROM per_minute”. What happens, and why?

Round 2:

Fix it using DuckDB’s Quack protocol, so a dashboard can query the same database while the writer keeps running. (Quack lets one DuckDB process serve its databases to other DuckDBs over the network. It’s a beta in DuckDB 1.5.x, goes stable with v2.0, and is in the v2.0 preview builds — use whichever version you have.)

Things to consider:

Keep everything on localhost while you experiment.

Reveal solution

This puzzle we put a database in the middle of the log pipeline, ran into DuckDB’s file lock, and used Quack to get around it.

Step 1 - what happens:

duckdb -readonly log.db -c "SELECT * FROM per_minute"

The second process fails with a lock error: “Could not set lock on file … Conflicting lock is held in … duckdb”. DuckDB lets one process at a time have a database file open, and that holds even when the second process only wants to read. The writer’s duckdb process stays open for as long as the stream runs, so nobody else can look at the table until it ends.

Step 2 - Quack:

Quack flips the arrangement. One process owns the database and serves it, and everyone else connects to it instead of opening the file. Concurrent read-write access to the same database is the first use case listed in Quack’s own FAQ. The writer stays the owner, since it has to hold the file anyway. We start the server from the same stream, in BEGIN:

tail -n +1 -F access.log | awk '
BEGIN {
  print "INSTALL quack; LOAD quack;"
  print "CREATE TABLE IF NOT EXISTS per_minute (minute TIMESTAMP, requests INTEGER, errors INTEGER);"
  print "CALL quack_serve(token = \047my_token\047);"
  fflush()
}
{
  m = $1 " " substr($2, 1, 5) ":00"
  if (m != cur) {
    if (cur != "") {
      printf "INSERT INTO per_minute VALUES (TIMESTAMP \047%s\047, %d, %d);\n", cur, total, errs
      fflush()
    }
    cur = m; total = 0; errs = 0
  }
  total++
  if ($5 >= 500) errs++
}' | duckdb log.db

And the dashboard, from another terminal:

duckdb -c "
  ATTACH 'quack:localhost' AS qk (TOKEN 'my_token');
  CONNECT qk;
  SELECT strftime(minute, '%H:%M') AS minute, requests, errors
  FROM per_minute
  ORDER BY minute;
  DISCONNECT;
"

The dashboard never opens log.db. It attaches to the server with a token, and after CONNECT its queries run on the server side, so there’s no file lock to fight over. Without the token the client can’t connect at all. Run it in a loop for a live view:

while true; do duckdb -c "..."; sleep 5; done

(Note: On linux just use watch)

The things to consider:

Why did the read-only reader fail? The lock is about the file, not about what you intend to do with it.

Which process should own the database? The long-lived one that has to hold the file anyway. Everyone else connects.

Does the database fix the last-minute problem from #023? No. The final minute never gets written, because awk still only knows a minute is over when a later timestamp arrives. The database changes who can read the rows, not when they exist.

Is a token enough? No. A token says who you are, it doesn’t encrypt anything. Quack talks over HTTP and leaves encryption to you, so beyond your own machine you’d put TLS in front of it with a reverse proxy. The Quack docs have a security page and a reverse proxy guide.

A word of caution:

Before v2.0, Quack is labelled beta, and its docs warn that function names and defaults can change. If a command here doesn’t match your version, check the current docs.

Compared with #024:

Polling a log file meant re-reading the whole file, catching half-written lines, and seeing a partial minute. Here the dashboard asks a database that only holds finished minutes, and a row is either there or it isn’t.

The lock error was the fatality. Quack is the friendship.