SQLite Forum

Why don't temporary on-disk databases support the WAL mode?
Login

Why don't temporary on-disk databases support the WAL mode?

(1) By Gwendal Roué (groue) on 2021-03-06 11:54:18 [source]

Hello,

While investigating an unexpected SQLITE_NOTFOUND error with SQLite 3.32.3 on iOS (groue/GRDB.swift#931), I discovered that temporary on-disk databases created with an empty path string given to sqlite3_open_v2 do not support the WAL mode:

  • PRAGMA journal_mode = WAL returns "delete", meaning the WAL mode is not applied.
  • sqlite3_file_control(..., SQLITE_FCNTL_PERSIST_WAL, ...) returns SQLITE_NOTFOUND, meaning the opcode is not recognized.

Those behaviors are consistent, and do not reveal any problem.

Yet I'm curious:

  • What prevents temporary on-disk databases from supporting the WAL mode?
  • Is it possible that temporary on-disk databases support the WAL mode in a future SQLite version?

Thanks for any hint!

Gwendal Roué

(2) By Stephan Beal (stephan) on 2021-03-06 16:44:43 in reply to 1 [link] [source]

Thanks for any hint!

i'm going to take a guess at this, without being 100% certain:

On non-Windows platforms temporary files are normally deleted as soon as they're opened. The file handle to them is still valid but the file is not visible in the filesystem and the file's resources will be reclaimed by the OS as soon as the opened file handle is closed (either via fclose() or exiting the app).

Because the file is deleted, it has no name in the filesystem, and thus cannot sensibly have a corresponding -wal file. (That begs they question how they handle journaling at all - presumably in memory, since it would be impossible to recover an on-disk journal for a transient/temporary file name.)

Again, just a guess. Someone here will eventually correct me if that's off base.

(3) By Gwendal Roué (groue) on 2021-03-06 17:07:37 in reply to 2 [link] [source]

This actually makes great sense, and sounds very convincing to me! Thanks :-)