I'm writing a distributed file system for SQLite where only one node can be the writer at any given time. Replica nodes are read-only and I want to return a SQLITE_READONLY error to the application if they try to perform a write.

With the rollback journal, I can detect open(2) with O_CREAT for the journal file and return EACCES which eventually turns into a SQLITE_READONLY for the client. That works great.

However, for WAL mode, I haven't been able to figure out a way to propagate a SQLITE_READONLY error code back to the client. I've tried returning errors when write locks are obtained (e.g. WAL_WRITE_LOCK, WAL_DMS_LOCK) but that conflicts with SQLite's lock retry loop and returns a SQLITE_PROTOCOL error. I've tried returning errors on the write(2) to the WAL but that returns a generic SQLITE_IOERR.

I don't see a way to return a SQLITE_READONLY error in WAL mode but I figured I'd reach out and see if anyone had any ideas. Thanks!

Why not just open the file as readonly using a URI filename after having made sure the journal_mode=wal?

That is,

del test.db
sqlite3 test.db "pragma main.journal_mode=wal"
sqlite3 file:test.db?mode=ro

You will note that after that final step the database connection has the database "test.db" opened read-only and it is in wal mode (so that a connection which opens the file readwrite can write to it). Locking/concurrency works as per normal.

Note that the "Open Flags" apply to the CONNECTION, and only by happenstance to the database which will be attached as schema "main". You probably want normal connection open flags and to specify the override using "attached database file schema" URI filename -- file:<filename>?mode=ro -- that is what URI filenames are for.

Perhaps something that's not clear, at least as far as I understand Ben's post,
is that he is writing a filesystem which SQLite uses, NOT using SQLite per-se.
I.e. not your typical use-case here, and what he is trying to enforce is below SQLite,
and he wants to translate (his custom) FileSystem errors enforcing his read-only requirement,
to appropriate SQLite errors. That will likely require Richard's assistance...

Hopefully I didn't misrepresent the situation Ben. Sorry if I did.

Yes, that's a good representation. Thanks for clearing that up, ddevienne.

Keith, unfortunately, I don't have control over the application side since folks can deploy this file system on their own. Also, the read-only state can change from moment to moment if the primary node changes.

It's definitely an unusual use case. Thanks for the help!

With the rollback journal, I can detect open(2) with O_CREAT for the journal file and return EACCES which eventually turns into a SQLITE_READONLY for the client.

What happens if you do this for both the *-wal and *-shm files in wal mode (return EACCES if O_CREAT or O_RDWR are set)?

I don't think it should attempt a write() or F_WRLCK lock in that case.

Dan.