> > > > > > The memory is being used by the statement journal, which you have in > > > memory. If the app did not set "journal_mode=memory" and > > > "temp_store=memory", SQLite would create a really large temp file > > > instead of using memory. Which would still be sub-optimal, but might > > > not run into the 32-bit limit. > > > > > > The reason the statement journal is growing so large is that SQLite > > > only truncates the statement journal when the outermost > > > sub-transaction is closed. Otherwise it just keeps on appending. i.e. > > > > > > BEGIN; > > > SAVEPOINT xxx; > > > ... > > > SAVEPOINT yyy; > > > ... > > > RELEASE yyy; -- does not truncate statement journal > > > COMMIT xxx; -- truncates statement journal > > > COMMIT; > > > > > > Your example has one explicit sub-transaction (equivalent to xxx) and > > > each DELETE statement is opening a second, implicit, sub-transaction > > > (equivalent to yyy). > > > > > > With the child records included, each DELETE statement is modifying 3 > > > db pages - one from table "bar", one from table "foo" and one from the > > > PRIMARY KEY index on "foo". 100,000 deletes, 4Kib page size, 3 pages > > > per delete == 1.2GiB. Or without the child records, just 1 page > > > modified per delete, so closer to 400MiB of memory. Without the > > > sub-transaction, the implicit sub-transaction created by each DELETE > > > becomes the outermost and so the statement journal doesn't grow much > > > at all. So not much memory used in that case. > > > > Another idea would be to use a deferred foreign key constraint. That way > > the DELETE operations will not need the statement journal at all. > > > > How can I get around implicit savepoint creation? > Why doesn't savepoint commit truncate a journal? > Why does journal grow even when there is nothing to delete in bar? > > Currently this limitation renders use of sqlite impossible, unless using ugly > hacks. > From the user's perspective, this overhead is unimaginable. This is completely > normal use of SQL yet overhead is above the wildest imagination :(
Also, I don not understand, how does it become outermost? Journal vener grows if there is only single transaction (or savepoint) aroun 'delete loop'. Why in case of just single transaction around deletes this does not happen? Are you saying there is no YYY savepoint? Or the journal can be truncated when omiting BEGIN oe XXX but not when they are both present? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
