Re: [sqlite] Determining size of insert row/query before inserting

[Stephan Beal]

On Thu, Aug 25, 2011 at 2:29 PM, Simon Slavin  wrote:

> 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.
>

A slight elaboration on that: this approach might not suffice on Unix
systems. Filesystems on Unix tend to (but not always) have a certain
percentage of the space reserved for the superuser (to keep user-space
processes from filling up the system, which can lead to all kinds of
problems on Unix systems, including (potentially) the inability to log in
and fix the problem). e.g. just because your tools report that a 100GB drive
still has 10GB free, 5GB of that might not be allocatable to non-root
processes.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining size of insert row/query before inserting

[Simon Slavin]

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

[sqlite] Determining size of insert row/query before inserting

[Pero Mirko]

How would you recommend to approach this problem:

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.

Is there any way to do this?

Also, another problem is that once disk space is low and query fails with
I/O error it leaves journal file. 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users