[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 ?
>
> 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

Thanks for the pointers; I think I am beginning to follow.

  The use of WAL is a persistent property of a database.  Clients use it
  or not when connecting, normally.

  WAL or DELETE (the normal rollback) are both sound for multiple
  writers, with different speed proeprties.

  sqlite3 docs say "WAL does not work over a network filesystem." but
  they do not say if this leads to an error, to corruption, or something
  else.

  I see what you mean about the docs being silent on existing processes
  and mode switching.  As what is being changed is the mode *of the
  database* and not the access mode of a single connection, it would
  seem a clear bug in sqlite3 if changing the mode isn't safe.


  As it is, qgis (by default) forces WAL mode, even if the database was
  not already in WAL, and even if there are other connections open.  Or
  it forces DELETE mode, similarly without checking.  So the concern
  about upgrading/downgrading comes from any setting of mode by any
  connection and any other existing connection.

  After digesting it all, I am left thinking that whether to use a
  geopackage in WAL mode or DELETE mode is a user decision, and that
  it's only safe to pick WAL if it's on a fs that supports it and it is
  known that no processes access the database via a remote filesystem.
  It's not enough for it to be local with respect to a writer that wants
  to use WAL; it has to be guaranteed that there aren't any remote
  writers (or even readers I think).

Therefore, I think the right approach is:
  1) let the user do "pragma JOURNAL_MODE=WAL;" if they want
  2) when opening the database, don't do anything about JOURNAL_MODE;
  just use it the way it is set.

(nI personally will leave it off, because 1) I don't like "read leads to
write" and 2) my workload is mostly reading for display and when I edit,
I'm clicking the UI and speed is not important.)

I am guessing that if OGR_SQLITE_JOURNAL is not set, the mode is not
changed, meaning the mode that the file already is in is used.  The gdal
docs didn't seem to explain and I'll have a look in the sources.

-------------- 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/07ae4ff9/attachment.sig>