[Qgis-user] Readonly gpkg

[Greg Troxel]

Even Rouault <even.rouault at spatialys.com> writes:

>> So it looks like qgis is also resetting the mode to delete, or perhaps
>> GDAL is doing that.   I haven't found that in the sources so far.
>
> Both attempt at doing this:
>
> - QGIS at
> https://github.com/qgis/QGIS/blob/master/src/core/providers/ogr/qgsogrproviderutils.cpp#L1172
> to #L1230 when it closes the last connection instance of a GPKG file

Thanks, I see that this code forces mode to DELETE.

> - and OGR too :
> https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp#L367

I think that's something else, disabling "persistent WAL" which is about
leaving the no-longer-used wal/shm files in the filesystem, so that
those that can access the database file but don't have write permission
for the directory will see fewer errors.

https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal

I have a gpkg in WAL mode currently and ran ogrinfo on it, and the mode
was still WAL afterwards.  I'm not sure that hits the code path you
referenced, though.

> Probably the most reasonable & simple change for QGIS would be turn
> WAL only when QGIS update mode is turned on. Would require testing
> that deadlocks don't occur with existing readers prior to turning WAL
> on (or accept that risk...)

I think the simplest and safest change is to remove all code that
changes the journal mode as a side effect of access.  People that want
WAL can pragma it on, with DBA hat on.  In my view sqlite's WAL option
breaks the previous concept that you can have a database without
understanding databases, and as long as it's a DBA choice, that's ok.

Separately from the "DBA chooses journal mode" view, I feel that
constantly flipping the journal mode is asking for trouble.   I suppose
one could write a test with N processes that each connect, set a random
choice of WAL or DELETE, wait a bit, do a transaction to increment a
value, perhaps repeat that, and then if they set WAL set DELETE and
exit, as a way to look for races.   Maybe sqlite3's own tests already do
that.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220112/750ed413/attachment.sig>