[pfx] Re: fatal: dict_sqlite_lookup: [...] SQL prepare failed: attempt to write a readonly database?

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]