<perg...@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".
signature.asc
Description: PGP signature
_______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user