[Qgis-user] Readonly gpkg

[Greg Troxel]

<pergler at gmail.com> writes:

> It feels it would be best to default to not mucking around with a gpkg's setting at all unless and until we're writing to it (editing existing layers or creating new ones). 
> When a new layer is added or existing layer opened for editing, then
> accept the gpkg's current setting or change it to WAL or DELETE, based
> on that application's setting. And as a service to the user,
> facilitate changing it for a specific gpkg file (for instance via
> browser).

I would agree with the first half-sentence and rephrase:

  I think it's best for programs that access geopackages to refrain from
  changing database properties as a side effect of access, period.

I think an important point behind this is that journal mode is a
property of the database, not of a program's access to a database.
And, which is appropriate is a choice that affects all aspects and all
use of the database.

I don't see any concept of DEBUG at

  https://sqlite.org/pragma.html#pragma_journal_mode

In reading that, note (in the context of storing geodata):

  TRUNCATE and PERSIST are flavors of DELETE, and I am not aware of
  evidence that they significantly help in any actual relevant
  situations.

  MEMORY is unsafe with respect to crashes, and thus should not be used.

  OFF is unsound and should never be used; it only makes sense for a
  database with one client that is always recreated on program start.

So I think we are talking about a subset of that world, where there is
only DELETE and WAL.  I'll assume that was a wordo and have edited.
 
> As I think about it, seems the application-level setting needs to have four levels?
> - Create new gpkg files as WAL, use existing setting for existing gkpg [read and write]
> - Create new gpkg files as DELETE, use existing setting for existing gkpg [read and write]

This is adding in database administrator (DBA) features, which is ok,
but I'm also not sure it's necessary.

DELETE should be the default, because it doesn't have the 'reading leads
to writing' problem, and because WAL is unsafe on remote filesystems, so
enabling requires the DBA to determine that it will not lead to unsound
behavior.

So another view is "just don't ever set any journal options".  Then if
the DBA wants to set WAL, they can do that.  gdal could have an option
for that, either an env var to set it on create, or a command to set it
on a geopackage.  And the qgis DB browser could offer DBA features.  a
big point is that journal mode for a geopackage is really not about
qgis; it's about all users of the geopackage.

> - Set to WAL whenever writing to gpkg
> - Set to DELETE whenever writing to gpkg

I don't think this option should exist.   I view it as a bug for a
program that accesses a database to change the journal mode, unless that
program is acting to do something on behalf of a DBA, and changing for
every write is not DBA behavior.

If there are multiple clients and they have different settings, they'll
fight each other.



I also note that I haven't seen any real benchmarks on real qgis usage
that shows that WAL improves performance over DELETE in a way that
matters.  My own perception, with relatively light write workloads, is
that the sqlite transaction commit cost of an entire qgis sessions is
negligible.  That also argues for "it's ok for almost all geopackages to
be in DELETE mode".
-------------- 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/530e2d24/attachment.sig>