[Qgis-user] Readonly gpkg

[Greg Troxel]

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

>> 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.
> WAL is really a programmer level concept. I'm not sure a QGIS advanced
> user/admin should be aware of that.

I think it's a DBA-level concept.    And perhaps it's reasonable to let
QGIS have a tool to let DBAs configure things.

>> 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.
>
> If you do no turn WAL on when editing, the following QGIS tests will
> fail (and some real world situations like where you have a huge layer
> being refreshed in the background while trying to edit it):
>
> https://github.com/qgis/QGIS/commit/b6b8759efbeb833d0d3dbf6df008087701361ad3#diff-56354e2446fe2cb6d1ee92d4e984091172e964e90f3be4d3d42276e033c4986eR92

I don't immediately grasp that, but it seems wrong to say that a
particular journal mode needs to be used to get the right behavior.  As
I read the sqlite3 docs, the journal mode should only affect efficiency,
not correctness, and sqlite3 should provide the same semantics
guarantees in both cases.

So I think this is evidence of a bug someplace in QGIS/GDAL/sqlite3, and
switching to WAL is a workaround, not a fix.



I don't think it's reasonable to always flip WAL on, because per the
docs WAL is only sound if there is no remote access, plus it breaks the
reads-do-not-write property.  I realize qgis checks if *it* is accessing
locally, but it cannot know that nobody else will access the same
geogpackage file over a remote file system.  Only the DBA can know that
this is not part of the larger operational config/plan.


The underlying big issue here is that sqlite3 sort of offers to let
people use databases without understanding them, and without having some
clue in a DBA role, like you'd expect with a postgis/postgresql setup.
But it seems that once there is any WAL, sqlite3 usage is not longer in
the "does not need to be understood" category.    That's a lot of why I
favor "let the DBA turn on WAL if they want, and don't change it
programmatically by default'.
-------------- 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/20220113/0ad2092b/attachment.sig>