6 ms·

From this (linked https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous), does anybody understand EXTRA? > EXTRA

by nh2 1y ago

From this (linked https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous), does anybody understand EXTRA? > EXTRA provides additional durability if the commit is followed closely by a power loss. means? How can one have "additional" durability, if FULL already "ensures that an operating system crash or power failure will not corrupt the database"? Is it that FULL only protects against "corruption" as stated, but will still lose committed transactions? It seems so from the points on https://stackoverflow.com/questions/58113560/during-power-loss-sqlite-rollbacks-my-db-to-a-point-before-begin https://stackoverflow.com/questions/58113560/during-power-lo... Which is also quite nasty. I want my databases to be fully durable by default, and not lose anything once they have acknowledged a transaction. The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash, after already having acknowledged it to a third-party over the network.

charleslmunger 1y ago
>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. EXTRA provides additional durability if the commit is followed closely by a power loss. It depends on your filesystem whether this is necessary. In any case I'm pretty sure it's not relevant for WAL mode.
agwa 1y ago
This is what the documentation (https://sqlite.org/pragma.html#pragma_synchronous 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 1y ago
  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.
  - nh2 1y ago
    > 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 https://sqlite.org/forum/forumpost/ec171a77a3
  - ncruces 1y ago
    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 1y ago
      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).
  - SQLite 1y ago
    > 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.
mrkeen 1y ago
> The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash That's why they don't try to do it that way! But it's still an informative way to think about it. Also, while we're discussing defaults, your ACID db is probably running at READ COMMITTED by default, meaning that your bank transactions are vanishing/creating money: * You read accounts A and B($30) in order to move $5 between them. The new balance for B should be $35. Just before you write the $35, someone else's transaction sets B to $100. Your transaction will proceed and blindly set it to $35 anyway. But to your overall point, I'm also frustrated that these systems aren't as safe as they look on the box.
- nh2 1y ago
  > your ACID db is probably running at READ COMMITTED by default You're probably refering to PostgreSQL. Yes, I am also frustrated that that doesn't default to SERIALIZABLE. I do wish the top two open-source "ACID" DBs (Postgres and SQLite) used guaranteed-safe, zero-surprise defaults.
  - lanstin 1y ago
    It isn’t worth it. Mostly financial transactions are done via append only ledgers, not updating; two phase auth and then capture; a settlement process to actually move money, and a reconciliation process to check all the accounts and totals. Even without DB corruptions they have enough problems (fraud and buggy code) with creating money and having to go back and ask people to more money or to give them more money so they have those systems in place any ways.
    - billywhizz 1y ago
      yes. most folks don't seem to understand this. but, you can get something approaching such guarantees if you are able to limit yourself to something as (seemingly) simple as updating a ledger. this approach is used in a lot of places where high performance and strong consistency is needed (see e.g. LMAX disruptor for similar). https://tigerbeetle.com/ https://tigerbeetle.com/
    - 1y ago
fkrlook 1y ago
This makes me so sick. For years and years I’ve gotten the vibe from SQLite that it never took being a reliable database seriously, but I bought into the hype for the past several years that it was finally a great DB for using in production, and then this. I swear. Sure, change the default config for now and make it actually behave in a sane way so that it doesn’t lose your data. But later- use a real database.
guenthert 1y ago
No corruption doesn't imply no data loss. Reverting to an earlier, consistent, state is in some situations acceptable (think Unix fsck), in others one might depend on committed transactions to have indeed been recorded as such. I'd think SQLite isn't typically used for bank applications, but rather to keep your web browser's bookmarks and such.
- fooster 1y ago
  Even worse because browser bookmarks are not exactly a high volume use case where an extra fsync matters at all.

by nh2 1y ago

charleslmunger 1y ago

>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. EXTRA provides additional durability if the commit is followed closely by a power loss. It depends on your filesystem whether this is necessary. In any case I'm pretty sure it's not relevant for WAL mode.

agwa 1y ago

This is what the documentation (https://sqlite.org/pragma.html#pragma_synchronous 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 1y ago
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.
- nh2 1y ago
  > 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 https://sqlite.org/forum/forumpost/ec171a77a3
- ncruces 1y ago
  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 1y ago
    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).
- SQLite 1y ago
  > 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.

mrkeen 1y ago

> The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash That's why they don't try to do it that way! But it's still an informative way to think about it. Also, while we're discussing defaults, your ACID db is probably running at READ COMMITTED by default, meaning that your bank transactions are vanishing/creating money: * You read accounts A and B($30) in order to move $5 between them. The new balance for B should be $35. Just before you write the $35, someone else's transaction sets B to $100. Your transaction will proceed and blindly set it to $35 anyway. But to your overall point, I'm also frustrated that these systems aren't as safe as they look on the box.

nh2 1y ago
> your ACID db is probably running at READ COMMITTED by default You're probably refering to PostgreSQL. Yes, I am also frustrated that that doesn't default to SERIALIZABLE. I do wish the top two open-source "ACID" DBs (Postgres and SQLite) used guaranteed-safe, zero-surprise defaults.
- lanstin 1y ago
  It isn’t worth it. Mostly financial transactions are done via append only ledgers, not updating; two phase auth and then capture; a settlement process to actually move money, and a reconciliation process to check all the accounts and totals. Even without DB corruptions they have enough problems (fraud and buggy code) with creating money and having to go back and ask people to more money or to give them more money so they have those systems in place any ways.
  - billywhizz 1y ago
    yes. most folks don't seem to understand this. but, you can get something approaching such guarantees if you are able to limit yourself to something as (seemingly) simple as updating a ledger. this approach is used in a lot of places where high performance and strong consistency is needed (see e.g. LMAX disruptor for similar). https://tigerbeetle.com/ https://tigerbeetle.com/
  - 1y ago

fkrlook 1y ago

This makes me so sick. For years and years I’ve gotten the vibe from SQLite that it never took being a reliable database seriously, but I bought into the hype for the past several years that it was finally a great DB for using in production, and then this. I swear. Sure, change the default config for now and make it actually behave in a sane way so that it doesn’t lose your data. But later- use a real database.

guenthert 1y ago

No corruption doesn't imply no data loss. Reverting to an earlier, consistent, state is in some situations acceptable (think Unix fsck), in others one might depend on committed transactions to have indeed been recorded as such. I'd think SQLite isn't typically used for bank applications, but rather to keep your web browser's bookmarks and such.

fooster 1y ago
Even worse because browser bookmarks are not exactly a high volume use case where an extra fsync matters at all.