On 4/5/19 11:14 AM, Arthur Blondel wrote: > I have enough disk space. I just limit the database file size artificially > for testing purpose as you can see. > There is no problem of privilege and there is nothing else than the code I > sent. No other access to the DB. > I'm using sqlite 3.16.2
As has been pointed out, one issue is that not all records, even if the 'same' take the same space, so deleting one record may not make enough room for another. Another issue is that for indexes, not all free space are the same, indexes keep similar values together in the index, so adding a row may need to find related space for an index, or you need to delete enough rows to either open space where needed or to free a full page of the index to let that page be used in the newly needed space for the index. I will admit that these are in a way esoteric implementation dependent details, so might not seem obvious, but they do explain the 'strangeness' that you see. Many data structures when running at a capacity limit can demonstrate these sorts of strangeness. Trying to tightly control resource usage is a tricky problem, and sometimes you need to think carefully about what you goal actually is (not what solution you think will work). Putting your hard limit on the base size of the database does put a hard limit on the size (in bytes) of the database, but may provide an unexpectedly low capacity of records in the worse case (and running data structures at this sort of limit tends to create at times conditions close to worse case), at the cost that the time to insert a record can grow significantly. If you really have plenty of disk space, than establishing a record limit in the database, and when you are at it removing one record for every record added, will smooth out the access time, at the cost of possibly higher disk usage at times (but maybe a better ratio of size per records). -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users