Hello !
I'm applying the strategy bellow on two applications executing in parallel
but it doesn't work, I mean both applications update the database but the
wall grows unbound.
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 )
??????? {
??? ??? stmt_find.reset();
??? ??? db.exec_dml("PRAGMA wal_checkpoint(FULL);");
??? ??? stmt_find.bind(1, id);
??? ??? count = 0;
??????? }
??? }
??? stmt_find.finalize();
??? stmt_update.finalize();
}
Cheers !
> Fri Mar 18 2016 03:50:06 PM CET from "Domingo Alvarez Duarte"
><sqlite-mail at dev.dadbiz.es> Subject: Re: [sqlite] Wall checkpoint not
>running
>
> 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_updatebind(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();
>
> ?
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
