wal-mode and checkpoint

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

wal-mode and checkpoint

Cecil Westerhof-5
I changed from the default delete mode to wal mode. I had some strange
results, but it is working now.

One of the programs is a service and can run for weeks. That is why I
decided to call every hour:
    PRAGMA WAL_CHECKPOINT(TRUNCATE)

I was wondering what people on this list thought about this, because after
the wal becomes bigger as 4 MB a checkpoint is (normally) done, but then I
noticed that for at least one reason it is a good idea.

When I do in sqlitebrowser:
    PRAGMA TABLE_INFO(messages)

sqlitebrowser thinks the database has changed and checkpoint does not work.
Luckily I output a message when it goes wrong and that is why I noticed it
and could do revert changes in sqlitebrowser.

Anybody an idea why this happens?


Another strange thing I saw was that when I have a database open in
sqlitebrowser that after termination of a program the wal file is not
written to the database. So I added a checkpoint to the end of my programs
also.

Any idea why this happens?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wal-mode and checkpoint

Simon Slavin-3
On 2 Sep 2018, at 2:43pm, Cecil Westerhof <[hidden email]> wrote:

> When I do in sqlitebrowser:
>    PRAGMA TABLE_INFO(messages)

Just for peace of mind, since you are reporting unexpected behaviour, please run an integrity_check.

Certain cleaning-up jobs are done only when the last connection to the database is closed.  So if one program is holding the database open you may find that these tasks are not completed.

Do you often leave sqlitebrowser running when you're not doing anything with it ?  Perhaps just as a background or minimised window on your screen ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wal-mode and checkpoint

Cecil Westerhof-5
2018-09-02 17:31 GMT+02:00 Simon Slavin <[hidden email]>:

> On 2 Sep 2018, at 2:43pm, Cecil Westerhof <[hidden email]> wrote:
>
> > When I do in sqlitebrowser:
> >    PRAGMA TABLE_INFO(messages)
>
> Just for peace of mind, since you are reporting unexpected behaviour,
> please run an integrity_check.
>

As expected that gave OK. The problem was that sqlitebrowser thought
something had changed while it had not.



> Certain cleaning-up jobs are done only when the last connection to the
> database is closed.  So if one program is holding the database open you may
> find that these tasks are not completed.
>

As I understood it, it should be done when the last writer has closed. But
maybe the fact that sqlitebrowser can write automatically makes it a writer.



> Do you often leave sqlitebrowser running when you're not doing anything
> with it ?  Perhaps just as a background or minimised window on your screen ?
>

Yes, I find it very handy when I want to check upon something to have it
open. Maybe I should rethink that. But just using:
    PRAGMA WAL_CHECKPOINT(TRUNCATE)

solves the problem that is created by having it open. So I think it is not
a problem (anymore).

And probably I am going to use integrity_check and foreign_key_check also.
Better to careful as not careful enough. ;-)

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users