@Rowan; First off, whether the OS or SQLite is ACID or not, if you pull the plug on your hardware, all bets are off on whether it'll BOOT, let alone recover a single transaction. I get that this could be a useful tool when doing disaster proofing, but, at that stage in the game of bulletproofing, you can't win every battle, and you're running into that at 100 miles an hour.
Next, SQLite *CAN* tell the OS that it is done with its data, that the OS can close file handles, and that the OS can flush the cache to the disk. Whether or not the OS, drivers, or disk drive obeys the command is an entirely different story. Now for my spiel; SQLite is nothing more than part of a program run by the OS. It completely relies on whatever the OS tells it. If the OS tells it that things are OK, then that is all that can be done. SQLite can't take on the responsibility of making sure that my taxes are done right, it won't ever wash my car (I'd be afraid the T would scratch the paint anyways) and it absolutely cannot defend against the lies an OS gives it. Believe me, computers are sometimes worse liars than humans are when it comes to internal stability. Look at SMART and the number of times it does error correction. Were you aware that digital TVs do error correction when getting data from your PVR, Satellite receiver, or cable TV due to electrical noise? Looks all good to you while you're watching the tube, but it is constantly correcting errors and lying that things are OK. SQLite doesn't manage your OS, it doesn't manage the firmware running on the drives. It doesn't care whether it runs over SATA, IDE, SCSI, USB, Firewire, Ethernet, or SAN. It relies on the OS to get whatever data it asks for, in either a read or write situation, and depends entirely on the OS to get the job done. SQLite does not manage the sector by sector reads and writes, it doesn't handle the physical writing of the bits to the platter. The OS handles that via its drivers, and if the drivers lie, or the OS isn't resilient enough for your needs, find a different OS or file system. The problem can also persist beyond the OS. The hard drives own firmware can lie to the OS. Error correction happens after it tries to write the data. Some drives will do the write attempt once, fail but still send back the OK, try again, and THEN send a fail back at the next operation that may not even be related to your program. Going back to the OS, I've seen instance, in both Windows and Linux based platform (RH and Debian) where file sizes do not change even though streams of data are being pumped into that particular file. I've seen situations where files have been deleted in Linux (Not allowed in Windows) and data still gets dumped into the now deleted file. Is it the programs fault that the file is physically deleted but the program isn't aware? Is it supposed to check every single time it writes a byte to see if the file exists, if the partition is healthy, and the root file system passes a FSCK at every commit? So yes, you HAVE to blame the file system and/or OS because SQLite isn't managing a file system. It manages a stream of data packaged into particular lengths of raw data we call pages, tells the OS that it needs to read a certain set of bytes from a specific number of bytes from the beginning of a file. SQLite doesn't know or care what sector the bytes live at on the drive physically. It doesn't know about fragmentation, it doesn't know what size the clusters are, or anything that is important for file storage. On Mon, Feb 1, 2016 at 6:53 AM, Simon Slavin <slavins at bigfraud.org> wrote: > > On 1 Feb 2016, at 11:24am, Rowan Worth <rowanw at dugeo.com> wrote: > > > > I take your point, but as Florian pointed out it's not just one file > > system; its a somewhat well known quirk of POSIX fsync. > > > http://blog.httrack.com/blog/2013/11/15/everything-you-always-wanted-to-know-about-fsync/ > > > > It's a bit disingenuous to say "the file system requires a particular > dance > > to ensure an unlink persists on disk, but the dance is excessively > complex > > so we're going to skip it and blame any problems on the file system." The > > fact remains that sqlite returned SQLITE_OK for a transaction that never > > succeeded, because it didn't take the steps required for the i/o to hit > > disk. > > Well, that's a fair point. The problem is that SQLite does not know it's > running on POSIX. It does, however, have different VFSes for different > platforms. So the logical place to put this would be in the VFS. At least > that's what it looks like to me but I don't know much about the programming > that goes into SQLite. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
