Hello !
After writing my problem and thinking about it I found that my unique reader
probably is the one blocking the checkpoint, so I modified it and now wall
file do not grows unbound.
But how to coordinate several applications to not been doing redundant
checkpoints ?
Maybe we need a pragma that tells us how many pending transactions exists
before proceed with a checkpoint ?
Something like "PRAGMA wal_checkpoint_pending_transactions" ?
Cheers !
local db = SQLite3("hacker-news-items.db");
db.exec_dml("PRAGMA synchronous =OFF;");
local function setItemsFieldNullOnEmpty(field)
{
??? print("setItemsFieldNullOnEmpty", field);
??? local stmt_find = db.prepare(format("select id from items where id > ?
and %s='' ", field));
??? local stmt_update = db.prepare(format("update items set %s=null where
id=?", field));
??
??? local count = 0;
??? stmt_find.bind(1, 0);
??? while(stmt_find.next_row())
??? {
??????? local id = stmt_find.col(0).tointeger();
??????? print("now updating id =", id, field);
??????? stmt_update.bind(1, id);
??????? stmt_update.step();
??????? stmt_update.reset();
??????? if( (++count > 1000) == 0)
??????? {
??? ?????? /*
??? ?????? //coordination between several applications
?????????? local pending = db.exec_dml("PRAGMA
wal_checkpoint_pending_transactions;");
??? ?????? if(pending <= count) //soemone else did a checkpoint
??? ?? ? ? {
??? ?????????? count = 0;
??? ?????????? continue;
??? ?????? }
??? ?????? */
?????????? stmt_find.reset();
??? ?????? db.exec_dml("PRAGMA wal_checkpoint(FULL);");
?????????? stmt_find.bind(1, id);
??? ?????? count = 0;
??????? }
??? }
??? stmt_find.finalize();
??? stmt_update.finalize();
}
foreach(field in ["title", "url", "comment"])
setItemsFieldNullOnEmpty(field);
db.close();
?
