[Qgis-user] Something to think about GeoPackage and WAL

[Even Rouault]

On jeudi 12 janvier 2017 10:17:52 CET Jukka Rahkonen wrote:
> Hi,
> 
> I did not manage to send mail to QGIS users, but see this:
> http://gis.stackexchange.com/questions/224188/geopackage-error-is-mounted-an
> d-in-wal-mode-this-combination-is-not-allowed
> 
> Could it mean issues which end users can't understand if GeoPackage is
> left to WAL mode and user tries to use it from network drive or as
> read-only? Is it even possible to change the journal mode in this case
> without moving the db into local disk?

Jukka,

(Adding qgis-user in CC)

WAL will not enabled by QGIS if the GeoPackage is located on a network driver (as best as we 
can detect that situation !) since the SQLite doc mentions that WAL isn't safe in that mode 
(see https://www.sqlite.org/wal.html)
And I don't think WAL can be enabled either on a read-only file/directory (the opening will go 
on even if we can't turn WAL on)

If moving a WAL enabled DB (the .gpkg plus the -wal and -shm) on a network share, then this 
should probably work, but not in a reliable way in a concurrent use case. If moving on a read-
only location, according to the doc, the database shouldn't be openable. If moving only the 
.gpkg file without the -wal and -shm, you'll probably get an outdated version of the database, 
or will not be able to open it at all. Not sure.

WAL is turned off by QGIS on layer removal, but QGIS must be the last program to have the 
file opened so that operation to be successful. If a GeoPackage is left in WAL mode (on a 
local disk), a workaround is to close all connections to it, and open / close it again with QGIS.

If there are interoperability problems, people can either define the
OGR_SQLITE_JOURNAL environment variable to DELETE or set the QGIS setting "/qgis/
walForSqlite3" (in advanced mode) to false, and this will prevent QGIS from enabling WAL on 
opening. The drawback is potential deadlocks in some situations where a reader and writer 
would run concurrently.

If the current situation causes more harm than good, we can change the  "/qgis/
walForSqlite3" setting default value to false. Together with enabling WAL, I made another 
change in the OGR provider that prevented a reader that has finished from iterating over 
features from being left in a ghost active state, but I guess there will be real read/write 
concurrent situations were deadlocks will appear.

I'm afraid there's no perfect solution. SQLite is a file-based database. One cannot ask for it 
the full power of server-based databases regarding concurrent use. 

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20170112/eafec71a/attachment.html>