Why does simply opening a geopackage (gpkg) "modify" the file?

Question

I'm really excited about transitioning to using geopackages instead of shapefiles, but I have one annoying problem.

My datasets are synced with Box, and I'm working with a really large .gpkg file (3.2 GB). If I simply open the file in R or QGIS just to check it out (don't make any changes to it), it "Modifies" the file. So, if I look at the date it was last modified in Finder, I see the date that it was "Modified" changes to the most recent date that I simply opened the file. So, if I then re-sync my files with Box, it always needs to re-sync the entire 3.2 GB .gpkg file. What is it about the .gpkg data format that causes it to be "Modified" just from opening the file? (I don't have a similar issue with .shp files).

Answer 1

QGIS and most(?) R libraries use the GDAL open-source libraries to open gpkg files. Up until version 3.4.1, GDAL opened the underlying sqlite database of a gpkg file in locked mode and with so-called WAL journaling mode. This had as a side-effect technically updating the file when it was closed and the journaling terminated, even if no actual edits were made.

Version 3.4.2 of GDAL provides a new NOLOCK option (https://github.com/OSGeo/gdal/pull/5207) which is utilized by the most recent versions of QGIS (https://github.com/qgis/QGIS/pull/47098) to not exhibit the behaviour you describe. Note that at time of writing (April 2022), not all distributed builds contain both the updated QGIS 3.24 but are also compiled against the required new version of GDAL that is needed. And the fix seems to have introduced some gremlins for opening gpkg from network shares on Windows directly that are in queue to be fixed (https://github.com/qgis/QGIS/pull/48119).

Bottom line is that this is being fixed for QGIS, but may take a bit longer to percolate to your install. It may take shorter or longer to percolate into your R setup, with whatever underlying packages to both use the necessary version of GDAL as well as to actually use those new options in opening files.

Finally, even before this was implemented, I was personally able to use the following workaround in QGIS, for gpkg files containing only layers that never get updated. After combining these layers in this "reference" gpkg, use the file system to make that file read-only. Inside the QGIS project properties, mark those layers read-only as well. When the layers get opened by your QGIS project, harmless zero-length .gpkg-wal and .gpkg-shm files are created (see GeoPackage in QGIS: what are .gpkg-shm and .gpkg-wal files?). But when the gpkg file gets closed, the read-only file system setting prevents the file from being modified. There was nothing to change in it anyway, so no problem gets caused.

Answer 2

There is a setting in the QGIS3.ini (chapter [qgis]):

walForSqlite3=false

You can edit the file manually or set this value via Python-Console:

QSettings().setValue("/qgis/walForSqlite3", False)

Next time opening any QGis-Projekt the GeoPackages (which are SQlite3-"Databases" internally) will be opened read-only and without wal-Files, if the layer is not edited.

Unfortunately, I don't know from which qgis-version this will work.

Answer 3

This solves my problem. I could live with gpkg files being updated when opened, even modified, but auxiliary files (-wal, -shm) were a headache for me. Setting the value at the beginning of my script does the trick. Using QGIS 3.28.8.