> On 12/16/2014 03:08 PM, Paul wrote: > >>>>> 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? > > Please don't mind my last message. > > > > I understand now, what is going on. Yet this limitation is pretty > > depressing. > > Is there no way in the future for things to change? > > Can't journal be truncated, or modified pages be merged, after each > > consequential > > implicit sub-transaction (YYY) release, while they are still in the cache? > > > > Is there any hope for me, except deferred FKs or DELETE FROM foo WHERE id > > IN (...)? > > There is now an update on the fossil trunk that should fix the problem > with ON DELETE CASCADE: > > http://www.sqlite.org/src/info/8c5dd6cc259e0cdaaddaa52ccfa96fee6b166906 >
Hi Dan! Thank you very much! I am very grateful for such a quick fix! You won't believe it, but I've just sent an update for 'INSERT OR REPLACE' few seconds ago :) Best regards, Paul _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
