After a cancelled query, the database runs without foreign keys #207

New Issue

2026-10-27T09:20:45+01:00

clawbot commented

2026-10-27 09:20:45 +01:00

internal/db/db.go opens the database with one connection in the pool (SetMaxOpenConns(1)) and turns on foreign keys once, with PRAGMA foreign_keys = ON on that first connection. SQLite keeps that setting per connection. When a query's context is cancelled while the query runs, the SQLite driver reports the connection broken, and database/sql closes it and later opens a new one. The new connection gets the _pragma settings in the database URL (WAL mode and the busy timeout in config.DefaultDBURL) but not foreign keys. From then on, ON DELETE CASCADE and every foreign key check are off until the server restarts.

Any query can be cancelled this way: the HTTP handlers pass the request's context to their queries, so a client that goes away mid-request is enough. The same cancel deletes an in-memory database, which is what made TestIntegrationKill fail at random (#205). Found by the worker on that issue; read from the code on a7f9beb.

Fix: every connection the pool opens has foreign keys on, whatever DBURL is set to. Change nothing else in how the database is opened.

Definition of done

After a query whose context is cancelled while it runs, PRAGMA foreign_keys on the database's next query returns 1.
This holds for the default DBURL and for a DBURL without any _pragma parameters.
A test in internal/db for both items.
One reviewed PR against next.

Model: opus-5-5

`internal/db/db.go` opens the database with one connection in the pool (`SetMaxOpenConns(1)`) and turns on foreign keys once, with `PRAGMA foreign_keys = ON` on that first connection. SQLite keeps that setting per connection. When a query's context is cancelled while the query runs, the SQLite driver reports the connection broken, and `database/sql` closes it and later opens a new one. The new connection gets the `_pragma` settings in the database URL (WAL mode and the busy timeout in `config.DefaultDBURL`) but not foreign keys. From then on, `ON DELETE CASCADE` and every foreign key check are off until the server restarts. Any query can be cancelled this way: the HTTP handlers pass the request's context to their queries, so a client that goes away mid-request is enough. The same cancel deletes an in-memory database, which is what made `TestIntegrationKill` fail at random (https://git.eeqj.de/sneak/neoirc/issues/205). Found by the worker on that issue; read from the code on `a7f9beb`. Fix: every connection the pool opens has foreign keys on, whatever `DBURL` is set to. Change nothing else in how the database is opened. ## Definition of done 1. After a query whose context is cancelled while it runs, `PRAGMA foreign_keys` on the database's next query returns 1. 2. This holds for the default `DBURL` and for a `DBURL` without any `_pragma` parameters. 3. A test in `internal/db` for both items. 4. One reviewed PR against `next`. Model: opus-5-5

clawbot referenced this issue

2026-10-27 09:21:07 +01:00

1.0: what stands between next and a release candidate #140

clawbot referenced this issue from a commit

2026-10-27 10:29:36 +01:00

Keep foreign keys on for every database connection (closes #207)

clawbot referenced a pull request that will close this issue

2026-10-27 10:33:49 +01:00

Keep foreign keys on for every database connection (closes #207) #208

clawbot commented

2026-10-27 10:34:00 +01:00

Fixed in #208. The server now adds _pragma=foreign_keys(1) to the database URL. The SQLite driver runs it on every connection it opens, including the one the pool opens after a cancelled query.

Model: opus-5-5

Fixed in https://git.eeqj.de/sneak/neoirc/pulls/208. The server now adds `_pragma=foreign_keys(1)` to the database URL. The SQLite driver runs it on every connection it opens, including the one the pool opens after a cancelled query. Model: opus-5-5

clawbot referenced this issue

2026-10-27 10:48:08 +01:00

Keep foreign keys on for every database connection (closes #207) #208

clawbot referenced this issue from a commit

2026-10-27 10:53:48 +01:00

Keep foreign keys on for every database connection (closes #207)

clawbot closed this issue

2026-10-27 11:14:02 +01:00

clawbot referenced this issue from a commit

2026-10-27 11:14:04 +01:00

Keep foreign keys on for every database connection (closes #207)

Sign in to join this conversation.