On Sun, Aug 09, 2026 at 01:39:33PM +0200, lama--- via Postfix-users wrote:
> Aug 9 05:33:37 example postfix/trivial-rewrite[20042]: fatal:
> dict_sqlite_lookup: /etc/postfix/sqlite-alias-vmaps.cf: SQL prepare failed:
> attempt to write a readonly database?
Prompt:
-------
What's required to use a read-only SQLite file for queries, without
running into problems with locks.
AI response:
------------
It depends on which of two situations you're in, because "read-only"
means two different things to SQLite.
Case 1: The file is truly static — nothing will ever write to it (a
shipped asset, a snapshot on read-only media, a file baked into a
container image). Open it with the immutable URI parameter:
file:/path/to/data.db?immutable=1
(You need URI filenames enabled — SQLITE_OPEN_URI in the C API,
uri=True in Python's sqlite3.connect.) With immutable=1, SQLite
skips locking entirely: it takes no locks, and it doesn't try to
create or read -wal/-shm/-journal sidecar files. This is the only
mode that works cleanly on a genuinely read-only filesystem, because
even a plain mode=ro open still wants to take shared locks and, for
a WAL-mode database, still needs the -shm file to be creatable. The
caveat is that immutable is a promise, not an enforcement — if
something does modify the file while you have it open, your queries
can return garbage or SQLITE_CORRUPT.
Two preparation steps matter for this case. First, if the database
was last used in WAL mode, checkpoint it and switch it back before
distributing: PRAGMA wal_checkpoint(TRUNCATE); PRAGMA
journal_mode=DELETE; — otherwise part of your data may live in a
-wal file you forgot to ship, and read-only opens of a WAL database
are finicky. Second, make sure there's no stale -journal or -wal
file sitting next to it, since those imply an interrupted
transaction that a read-only connection can't roll back (that's a
classic source of "unable to open database file" on read-only
mounts).
Case 2: The file is live — something else may write it, and you just
want lock-free reads. Here immutable is wrong (it would give you
corrupt reads). Instead:
Have the writer put the database in WAL mode (PRAGMA
journal_mode=WAL). In WAL mode, readers never block the writer and
the writer never blocks readers, which eliminates almost all
SQLITE_BUSY for read queries. Note WAL is a property of the database
file, set once, and it requires readers to have write access to the
directory (for the -shm file) — so WAL and a read-only filesystem
don't mix. Open your connection with file:data.db?mode=ro, and
optionally PRAGMA query_only=ON as a guard. Set PRAGMA busy_timeout
(e.g. 5000 ms) anyway, for the rare moments like checkpoints or if
the DB is in rollback-journal mode. Avoid network filesystems (NFS,
SMB) for the shared-writer case — SQLite's locking is unreliable
there. On a network mount with a truly static file, immutable=1 (or
nolock=1) sidesteps that too. So the short version: static file →
mode=ro&immutable=1 after checkpointing out of WAL; live file with a
writer → WAL mode plus mode=ro and a busy timeout, on a local disk
with a writable directory.
--
Viktor. 🇺🇦 Слава Україні!
_______________________________________________
Postfix-users mailing list -- [email protected]
To unsubscribe send an email to [email protected]
