[GRASS-user] turning sqlite journal off

[Jonathan Moules]

Hi Laura,

(I'm coming at this as someone familiar with SQLite but not GRASS)

As far as I can tell, the journal_mode PRAGMA is non-persistent. So if 
you set it within SQLite at the command line, then that setting is only 
going to remain set during that connection. GRASS uses its own 
connection so will use the default (DELETE) unless otherwise set.

The exception is WAL - Write Ahead Log. This setting persists. Take a 
look at https://sqlite.org/wal.html to see if it's suited to your 
use-case. Then if you set it, it should remain set when GRASS uses the db.

As far as I can tell doing a quick search of the GRASS code using 
github, the journal_mode is only set once, via the temporal python 
library to MEMORY - 
https://github.com/OSGeo/grass/search?utf8=%E2%9C%93&q=journal_mode&type=

The other way to work around this non-persistent journal_mode is: "Note 
also that the journal_mode cannot be changed while a transaction is 
active." - so keep a connection-with-transaction open via not-GRASS 
while GRASS is doing its thing.

Cheers,

Jonathan


On 16/05/2019 09:28, Laura Poggio wrote:
> Dear all,
> thanks for the answers and apologies if my question at the beginning 
> was not so clear.
> SQLITE can support multiple readers and only one writer. But it is not 
> the main problem.
> The issue here is the creation of the journal file. Each time a GRASS 
> session that modified the sqlite.db file is closed, the journal file 
> is created. This can take quite some time and during this time the 
> database is locked. This behaviour is expected.
> I can turn off the creation of the journal file (being aware of the 
> potential risks) using sqlite options and thus reducing the time the 
> database is locked. However these options do not apply when the file 
> is modified by GRASS.
> Is it possible to turn off the creation of the journal file when a 
> GRASS session is involved?
>
> Thanks!
>
> Laura
>
>
> On Thu, 16 May 2019 at 00:07, Nikos Alexandris 
> <nik at nikosalexandris.net <mailto:nik at nikosalexandris.net>> wrote:
>
>     * Markus Metz <markus.metz.giswork at gmail.com
>     <mailto:markus.metz.giswork at gmail.com>> [2019-05-15 22:55:13 +0200]:
>
>     >On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos
>     <pmav99 at gmail.com <mailto:pmav99 at gmail.com>>
>     >wrote:
>     >>
>     >> Hi Laura,
>     >>
>     >> This thread seems to be related:
>     >http://osgeo-org.1560.x6.nabble.com/SQLITE-db-locking-problem-td5182180.html
>     >> I also had a somewhat similar problem that was related to the
>     VACUUM
>     >command issued when closing a GRASS session (new session started
>     before the
>     >VACUUM of the previous session was finished)
>     >> If I understand this correctly, you are not supposed to
>     concurrently use
>     >the same sqlite database.
>     >
>     >Yes, this is a limitation of sqlite, and the GRASS-internal
>     sqlite driver
>     >has been adapted accordingly as much as possible.
>
>     Nevertheless, concurrent reading is allowed.  Reading the
>     discussion so
>     far, one may think that no concurrent use is possible at all.
>
>     See also https://sqlite.org/whentouse.html :
>
>       "SQLite supports an unlimited number of simultaneous readers, but it
>       will only allow one writer at any instant in time."
>
>     And in https://sqlite.org/lockingv3.html see 'SHARED'.
>     Also, https://stackoverflow.com/a/4060838/1172302.
>
>     Nikos
>     _______________________________________________
>     grass-user mailing list
>     grass-user at lists.osgeo.org <mailto:grass-user at lists.osgeo.org>
>     https://lists.osgeo.org/mailman/listinfo/grass-user
>
>
>
> _______________________________________________
> grass-user mailing list
> grass-user at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/grass-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-user/attachments/20190528/13faae85/attachment.html>