This is what the documentation (https://sqlite.org/pragma.html#pragma_synchronous) says about EXTRA:

> EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode

So it only has an effect in DELETE mode; WAL mode doesn't use a rollback journal.

That said, the documentation about this is pretty confusing.

▲

nh2 4 days ago | parent [-]

Yes, I'm talking about the fact that sqlite in its default (journal_mode = DELETE) is not durable.

Which in my opinion is worse than whatever may apply to WAL mode, because WAL is something a user needs to explicitly enable.

If it is true as stated, then I also don't find it very confusing, but would definitely appreciate if it were more explicit, replacing "will not corrupt the database" by "will not corrupt the database (but may still lose committed transactions on power loss)", and I certainly find that a very bad default.

▲

SQLite 3 days ago | parent | next [-]

> sqlite in its default (journal_mode = DELETE) is not durable.

Not true. In its default configuration, SQLite is durable.

If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.

This behavior is what most applications want. You'll never get a corrupt database, even on a power loss or similar. You might lose a transaction that happened within the past second or so. So if you cat trips over the power cord a few milliseconds after you set a bookmark in Chrome, that bookmark might not be there after you reboot. Most people don't care. Most people would rather have the extra day-to-day performance and reduced SSD wear. But if you have some application where preserving the last moment of work is vital, then SQLite provides that option, at run-time, or at compile-time.

When WAL mode was originally introduced, it was guaranteed durable by default, just like DELETE mode. But people complained that they would rather have increased performance and didn't really care if a recent transaction rolled back after a power-loss, just as long as the database didn't go corrupt. So we changed the default. I'm sorry if that choice offends you. You can easily restore the original behavior at compile-time if you prefer.

	▲	charleslmunger 3 days ago \| parent [-]
		>If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored. How is this behavior reconciled with the documentation cited in my comment above? Are the docs just out of date?

▲

ncruces 3 days ago | parent | prev | next [-]

For DELETE (rollback) mode, and given the way fsync works, FULL should not lose committed transactions (plural) but it might lose at most one committed transaction.

Because DELETE depends on deleting a file (not modifying file contents), it much depends on the specific file system's (not SQLite's) journaling behavior.

	▲	nh2 3 days ago \| parent [-]
		I don't see how the journal delete depends any more on a specific file system's behaviour than the main data write: If a specific file system can decide to automatically fsync unlink(), it can equally decide to automatic fsync write(). In either case it is clear that (on Linux), if you want guarantees, you need to fsync (the file and the dir respectively).

▲

nh2 4 days ago | parent | prev [-]

> That said, the documentation about this is pretty confusing.

I now filed a suggestion to clarify the docs on this:

https://sqlite.org/forum/forumpost/ec171a77a3