SQLite pool connections re-run PRAGMA journal_mode=WAL, blocking concurrent thread/resume for over a minute

What issue are you seeing?

Summary

codex-state configures journal_mode(WAL) in SqliteConnectOptions
used by a lazy SqlitePool with max_connections(5).

SQLx applies the configured PRAGMAs whenever the pool opens a new
connection. Since WAL mode is persistent and changing/querying it through
the setter requires an exclusive database lock, opening another pooled
connection while an existing thread is writing can block unrelated
app-server requests for over a minute.

Affected code

codex-rs/state/src/sqlite.rs:

let options = SqliteConnectOptions::new()
    .filename(path)
    .create_if_missing(true)
    .journal_mode(SqliteJournalMode::Wal)
    .synchronous(SqliteSynchronous::Normal)
    .auto_vacuum(SqliteAutoVacuum::Incremental)
    .busy_timeout(Duration::from_secs(5));

SqlitePoolOptions::new()
    .max_connections(5)
    .connect_with(options)
    .await

What steps can reproduce the bug?

1. Start one app-server using a single CODEX_HOME.
2. Start a turn that is actively writing thread state.
3. Concurrently resume or read another thread.
4. The pool attempts to open another SQLite connection.
5. thread/resume stalls and eventually times out.

Observed logs:
thread/resume:
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;
elapsed=110.34s

thread/read:
SELECT ... FROM threads WHERE threads.id = ?
elapsed=61.41s

active turn:
INSERT INTO threads (...) ON CONFLICT(id) DO UPDATE ...
elapsed=61.46s

What is the expected behavior?

Opening another pooled connection should not reconfigure the persistent
SQLite journal mode and should not block an unrelated active thread.

Additional information

Suggested fix

Use one short-lived setup connection to:

1. Set auto_vacuum=INCREMENTAL only when creating a new database.
2. Read the current journal_mode.
3. Set WAL only when the database is not already in WAL mode.

Then create the connection pool without journal_mode or auto_vacuum
in its per-connection options.
A regression test can hold BEGIN EXCLUSIVE on one pooled connection
and assert that acquiring a second connection does not wait for WAL
reconfiguration.

Related

Related to #20213, but this report concerns repeated per-connection WAL
configuration within one process and one SQLite pool, rather than multiple
Codex processes sharing a home directory.