On 25 Aug 2011, at 10:52am, Pero Mirko wrote: > I want to determine how much disk space a certain insert will take before > actually executing it so I can fail insert before doing it rather than > trying to insert, then receive SQLite Disk I/O error when the disk is > filled. > > So I would determine first how much disk space is left, then determine > approximately how much next query will take on disk and if there is not > enough space fail before even trying to write.
SQLite stores information in your database split up into pages. Each part of your data: basic information, the data for a table, each index for a table, has pages assigned to it. All pages for a particular database file are the same size. A typical page size might be 2048 bytes. You can use a PRAGMA statement to find out how big the pages are for a particular database file. When SQLite needs to store new bytes it first tries to fit them into existing page assigned to that kind of data. So, for example, adding a row to a table might involve adding 240 bytes to one set of pages assigned to the data itself, 22 bytes to the pages for one index and 119 bytes to the pages for another index. If the new data fits into space already available in those pages, no new space might be needed to insert the new record. However, you might be particularly unlucky and you might need to grab three new pages. Without analysis of the current state of the database file there's no way to tell, in the above example, whether SQLite might need to grab 0, 1, 2 or 3 pages in order to save the new data. In addition, if you're using journaling (which you are by default) you have in the same folder on the disk a journal file which also grows and shrinks unpredictably depending on how you're managing your transactions. I think your overall best strategy here it not to run any system so close to the margin it's likely to fail. Define some amount of free space for your system -- for example 10Kb for an embedded system or 1Meg for a desktop computer -- and if you have less than that amount of space free, take emergency measures and refuse to accept new data. > Also, another problem is that once disk space is low and query fails with > I/O error it leaves journal file. Oh sorry, I now see you know that. > Apparently the database is not corrupted > as it seems to do auto-rollback and on next access of database the journal > file is gone. But why is it there when write fails due to low disk space? > > The process goes like this > 1. BEGIN EXCLUSIVE > 2. some INSERT > 3. sqlite3_step() (fails to low disk space) > 4. journal file is not deleted even if I do manual ROLLBACK... > > However, after closing database and reopening it journal is deleted and > apparently database can be accessed normally and it doesn't seem to be > corrupted although its size is not truncated to previous size before INSERT. Journal files are used to store information about pending transactions and rollback points (a little handwaving here for simplicity). If there are none of those, the journal file isn't needed. So when a database file is closed (has been properly closed by _close()) or if a file is open but no changes have been made to it, there's no need for a journal file to exist. If SQLite opens a database file which it knows nothing else is using and finds a journal file, it knows there was some sort of crash the last time the database was used. It uses the information in the journal file to restore the database to a non-corrupted state, then acts as if you closed and reopened the database file. Consequently, because there are no now pending transactions, the journal file disappears. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users