4 ms·

Good reply, thank you. Yes 200k SLOC is huge (modern development practices notwithstanding). SQLite creates temporary files at whim - nine different kinds! htt

Good reply, thank you. Yes 200k SLOC is huge (modern development practices notwithstanding). SQLite creates temporary files at whim - nine different kinds! https://sqlite.org/tempfiles.html https://sqlite.org/tempfiles.html I know how to atomically write a JSON file. But when I read, for example: "The temporary files associated with transaction control, namely the rollback journal, super-journal, write-ahead log (WAL) files, and shared-memory files, are always written to disk. But the other kinds of temporary files might be stored in memory only and never written to disk. Whether or not temporary files other than the rollback, super, and statement journals are written to disk or stored only in memory depends on the SQLITE_TEMP_STORE compile-time parameter, the temp_store pragma, and on the size of the temporary file..." My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know?

iainmerrick 6y ago
I know how to atomically write a JSON file. Are you sure? I’ve had a lot of trouble getting that to work reliably myself across multiple OSes. (In hindsight I wish I’d used SQLite!) This article gives a good explanation of the many difficulties: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know? Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved. But you could a) read through those docs, lengthy though they are, and/or b) trust the many testimonials saying SQLite is very, very robust and reliable.
- millstone 6y ago
  > Are you sure? No, and anyone who says yes is lying. (Lockless NFS exists and is no fun.) > Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved SQLite has set itself the horrible task of updating files in-place. I know of two reliable, simpler alternatives: 1. Appending to files through O_APPEND 2. Rewriting files through rename() If SQLite has different magic syscalls then I would very much like to learn.
  - Someone 6y ago
    I don’t think you can atomically append more than one byte to files in unixes (the write call can return after having written some but not all requested bytes) (Haven’t googled, but if that’s possible, I don’t see why write would have that limitation)
    - millstone 6y ago
      Yeah, and eventually we reach the best-effort bedrock. Maybe the file is on a NFS mount, you call write(), it goes over the wire, who knows what happens!
    - LunaSea 6y ago
      Interesting and confirmed in the write() syscall man pages. Thanks! Do you have any other resources regarding these types of low level "gotchas"? I remember PostgreSQL having such an issue two years ago for example.
  - moosebear847 6y ago
    Dan luu's article mentioned these two options as unreliable. What's the reason for the disparity?
- scaladev 6y ago
  Here's another good review of the pain you get if you want to get your data to disk safely. (SQLite does this for you automatically, BTW.) "Ensuring data reaches disk" https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/
  - ori_b 6y ago
    > (SQLite does this for you automatically, BTW.) Unless you're on nfs. Remote file locking is hard, and I don't think that any nfs implementation has gotten to the point where you can trust SQLite on it. SQLite does updates in place, which I would trust far less than a rename call.
pjc50 6y ago
> I know how to atomically write a JSON file. Sure. But that forces you to rewrite all the data at once. Once it becomes large or you require more frequent changes, that will impact performance.
exikyut 6y ago
> If I add this to my app, what will it actually do? How can I even know? Be really, really, *really*, unambiguously sure about whether your data was written or not, AND have high confidence that I/O errors (eg, power loss) in the middle of does of deletes won't scramble (or truncate) existing data. What you're looking at is the complexity required to solve for the wonderful tornado of "but it's my data really written???". But you don't have to deal with SQLite's implementation details in order for it to do its thing, which is what makes it so awesome (given is public domain status, what's more!).

iainmerrick 6y ago

I know how to atomically write a JSON file. Are you sure? I’ve had a lot of trouble getting that to work reliably myself across multiple OSes. (In hindsight I wish I’d used SQLite!) This article gives a good explanation of the many difficulties: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know? Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved. But you could a) read through those docs, lengthy though they are, and/or b) trust the many testimonials saying SQLite is very, very robust and reliable.

millstone 6y ago
> Are you sure? No, and anyone who says yes is lying. (Lockless NFS exists and is no fun.) > Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved SQLite has set itself the horrible task of updating files in-place. I know of two reliable, simpler alternatives: 1. Appending to files through O_APPEND 2. Rewriting files through rename() If SQLite has different magic syscalls then I would very much like to learn.
- Someone 6y ago
  I don’t think you can atomically append more than one byte to files in unixes (the write call can return after having written some but not all requested bytes) (Haven’t googled, but if that’s possible, I don’t see why write would have that limitation)
  - millstone 6y ago
    Yeah, and eventually we reach the best-effort bedrock. Maybe the file is on a NFS mount, you call write(), it goes over the wire, who knows what happens!
  - LunaSea 6y ago
    Interesting and confirmed in the write() syscall man pages. Thanks! Do you have any other resources regarding these types of low level "gotchas"? I remember PostgreSQL having such an issue two years ago for example.
- moosebear847 6y ago
  Dan luu's article mentioned these two options as unreliable. What's the reason for the disparity?
scaladev 6y ago
Here's another good review of the pain you get if you want to get your data to disk safely. (SQLite does this for you automatically, BTW.) "Ensuring data reaches disk" https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/
- ori_b 6y ago
  > (SQLite does this for you automatically, BTW.) Unless you're on nfs. Remote file locking is hard, and I don't think that any nfs implementation has gotten to the point where you can trust SQLite on it. SQLite does updates in place, which I would trust far less than a rename call.

pjc50 6y ago

> I know how to atomically write a JSON file. Sure. But that forces you to rewrite all the data at once. Once it becomes large or you require more frequent changes, that will impact performance.

exikyut 6y ago

> If I add this to my app, what will it actually do? How can I even know? Be really, really, *really*, unambiguously sure about whether your data was written or not, AND have high confidence that I/O errors (eg, power loss) in the middle of does of deletes won't scramble (or truncate) existing data. What you're looking at is the complexity required to solve for the wonderful tornado of "but it's my data really written???". But you don't have to deal with SQLite's implementation details in order for it to do its thing, which is what makes it so awesome (given is public domain status, what's more!).