[Qgis-user] Readonly gpkg

[Greg Troxel]

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

> The main issue I see is that it is unknown (and non documented AFAICS
> in SQLite documentation) what happens if :
>
> - reader A has opened the file with default journaling mode (for read-only)
>
> - reader B (other QGIS process for example, or possibly the same QGIS
> process like a renderer thread) has opened the file with default
> journaling mode (for read-only)
>
> - let's say A wants to edit and we thus enable WAL to avoid
> reader/writer blocks
>
> - is B which has opened without WAL still in a safe state ? Does it
> react properly to WAL having been enabled behind its back ?

Perhaps each read needs to be a transaction with taking and release a
shared-read lock.  And/Or the writer needs to wait until the readers
have released the read locks.

In a way this complaint of writing is not fair if sqlite3 is a 'real
database', but the attraction of it is specifically that it is less than
a full database management wise.

> The default enabling of WAL even in initial read-only access mode is
> to avoid that potential issue.
>
>
> There are currently way of disabling WAL (for advanced users) :
>
> - setting the OGR_SQLITE_JOURNALenvironment variable to JOURNAL
>
> - or setting the QGIS setting "qgis/walForSqlite" to false
>
> See
> https://github.com/qgis/QGIS/blob/master/src/core/providers/ogr/qgsogrproviderutils.cpp#L1005

It seems then that sqlite3 itself needs to be enhanced to avoid writing
until there's a write, in such a way that it can deal with the
cross-user locking.   Either that or move all that locking and
signalling into the above-sqlite access layer.

When I use pgsql, I expect to deal with a db, don't back up the data
dir, and run a pg_dumpall before backups and do backup that.  But
sqlite3 files in random places are files and treated like that, except
here they are acting more or less like pgsql in that reads lead to
writes, and one should use a different backup strategy.


Are you saying that if we disable the WAL option, then accesses are
still safe, but we have lower concurrency, because there is some locking
scheme to interlock writing and reading?    That seems better to me, for
most uses.   Perhaps a property in the layer to use WAL, otherwise not?

If so, it seems WAL mode should be taking that lock in shared-write
mode, where multiple can write but all must be in WAL mode, vs single
writer vs multiple reader.
-------------- 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/20220111/476d896f/attachment.sig>