Re: [Qgis-user] Readonly gpkg
Just as a general comment with GPKG, I am aware there were some concerns expressed in this community about issues with this format. Are these issues being progressed? I use spatialite in preference to gpkg in order to work around these issues. On 1/02/22 06:26, Even Rouault wrote: Resolution attempt in https://github.com/qgis/QGIS/pull/47098 Even ___ 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
Re: [Qgis-user] Readonly gpkg
Resolution attempt in https://github.com/qgis/QGIS/pull/47098 Even Le 11/01/2022 à 15:10, perg...@gmail.com a écrit : I’ve packaged a number of read-only layers (e.g. contours, watercourses) for my area of interest into one gpkg. However, QGIS writes changes to this gpkg every time it is opened, even if the layers themselves are never edited. I think this is related to SQLite wal journaling. It’s an annoyance since it’s a large file that gets unnecessarily reuploaded in every incremental backup, and when I’m in the field, that’s sometimes over a bad 3G cell connection. I’ve tried making the gpkg read-only, which hasn’t crashed anything in brief testing and generates no warnings. However, it feels risky, and clearly QGIS or OGR aren’t fully happy, since the temporary shm and wal files don’t get deleted on exit. So I’m hesitant to continue doing this for fear of a crash when I’m actually doing something useful. Is there a better way to tell QGIS to not write to a gpkg in which all layers are only being accessed read-only? Thanks! --- Martin Pergler ___ 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 -- http://www.spatialys.com My software is free, but my time generally not. ___ 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
Re: [Qgis-user] Readonly gpkg
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, We turn WAL on only because of the second bullet of https://www.sqlite.org/wal.html: "WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently." -- http://www.spatialys.com My software is free, but my time generally not. ___ 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
Re: [Qgis-user] Readonly gpkg
Even Rouault 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'. 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
Re: [Qgis-user] Readonly gpkg
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. 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 -- http://www.spatialys.com My software is free, but my time generally not. ___ 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
Re: [Qgis-user] Readonly gpkg
Even Rouault writes: >> So it looks like qgis is also resetting the mode to delete, or perhaps >> GDAL is doing that. I haven't found that in the sources so far. > > Both attempt at doing this: > > - QGIS at > https://github.com/qgis/QGIS/blob/master/src/core/providers/ogr/qgsogrproviderutils.cpp#L1172 > to #L1230 when it closes the last connection instance of a GPKG file Thanks, I see that this code forces mode to DELETE. > - and OGR too : > https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp#L367 I think that's something else, disabling "persistent WAL" which is about leaving the no-longer-used wal/shm files in the filesystem, so that those that can access the database file but don't have write permission for the directory will see fewer errors. https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal I have a gpkg in WAL mode currently and ran ogrinfo on it, and the mode was still WAL afterwards. I'm not sure that hits the code path you referenced, though. > Probably the most reasonable & simple change for QGIS would be turn > WAL only when QGIS update mode is turned on. Would require testing > that deadlocks don't occur with existing readers prior to turning WAL > on (or accept that risk...) 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. 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. 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
Re: [Qgis-user] Readonly gpkg
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
Re: [Qgis-user] Readonly gpkg
So it looks like qgis is also resetting the mode to delete, or perhaps GDAL is doing that. I haven't found that in the sources so far. Both attempt at doing this: - QGIS at https://github.com/qgis/QGIS/blob/master/src/core/providers/ogr/qgsogrproviderutils.cpp#L1172 to #L1230 when it closes the last connection instance of a GPKG file - and OGR too : https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp#L367 Probably the most reasonable & simple change for QGIS would be turn WAL only when QGIS update mode is turned on. Would require testing that deadlocks don't occur with existing readers prior to turning WAL on (or accept that risk...) ___ 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 -- http://www.spatialys.com My software is free, but my time generally not. ___ 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
Re: [Qgis-user] Readonly gpkg
Nyall Dawson writes: >> Nyall Dawson writes: >> >> > On Wed, 12 Jan 2022 at 11:00, Greg Troxel wrote: >> >> Right now, I think WAL/DELETE is a property of the sqlite3 file which >> contains the geopackage data. But it's a sqlite3 property, not a >> geopackage property. (And qgis whacks it on every open.) >> >> If I am following, you are suggesting a geopackage-level property (in >> some table?), and a rule that when a reader or write opens the file, if >> the sqlite3 setting does not match, the sqlite3 setting is forced and an >> error is logged? > > That's correct, but without the error logging. Maybe I'm being too CS-compulsive, but if the geopackage spec is extended to specify a sqlite3 journal mode, then it's a bug if the database is ever in a different mode. I was suggesting logging so that this bug would be noticed and could be fixed, rather then silently papered over. I read the gdal code, and if SQLITE_JOURNAL_MODE is not set, no forcing is done. So I wonder if anything other than qgis sets the journal mode currently. Also, it seems that *only* WAL is persistent. Otherwise "DELETE" is chosen on open. https://sqlite.org/pragma.html#pragma_journal_mode However with some quick command-line testing, it seems that the behavior is not quite from what I expect. With qgis open, I see wal/shm files as I'd expect from the current code, and the pragma returns wal. But when wgis exits, the mode has been [qreset to delete. So it looks like qgis is also resetting the mode to delete, or perhaps GDAL is doing that. I haven't found that in the sources so far. 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
Re: [Qgis-user] Readonly gpkg
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). "Whacking" it to WAL on any open (even if just reading data, making no edits) feels like not playing well with others. 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 DEBUG, use existing setting for existing gkpg [read and write] - Set to WAL whenever writing to gpkg - Set to DEBUG whenever writing to gpkg In all of this, just trying to be helpful. Ignore me if I'm out of my depth and just creating noise... Martin -Original Message- From: Nyall Dawson Sent: January 11, 2022 20:46 On Wed, 12 Jan 2022 at 11:40, Greg Troxel wrote: > > Right now, I think WAL/DELETE is a property of the sqlite3 file which > contains the geopackage data. But it's a sqlite3 property, not a > geopackage property. (And qgis whacks it on every open.) > > If I am following, you are suggesting a geopackage-level property (in > some table?), and a rule that when a reader or write opens the file, > if the sqlite3 setting does not match, the sqlite3 setting is forced > and an error is logged? That's correct, but without the error logging. Nyall ___ 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
Re: [Qgis-user] Readonly gpkg
On Wed, 12 Jan 2022 at 11:40, Greg Troxel wrote: > > > Nyall Dawson writes: > > > On Wed, 12 Jan 2022 at 11:00, Greg Troxel wrote: > >> > >> > >> writes: > >> > >> > A. Ideally WAL/DELETE would be a connection (in the sense of the > >> > Browser connections list) level decision changeable by the user, not a > >> > layer-level decision and not (as now) an application setting used for > >> > all gpkg connections. > >> > >> Fundamentally WAL/DELETE is a (changeable) property of an sqlite3 file, > >> and thus of a geopackage container. So exposing it as a db browser > >> property and allowing it to be changed, with a warning that it is a DB > >> Admin operation, not a connection operation, seems ok. > > > > Instead of exposing this as a per-workstation, QGIS only property, I'd > > propose instead that the property be set somewhere in the gpkg > > metadata itself, so that regardless of which user opens it in a > > multi-user environment, they'll always get the property set by the db > > administrator. (Maybe this could even become a future formal gpkg > > extension...?) > > Right now, I think WAL/DELETE is a property of the sqlite3 file which > contains the geopackage data. But it's a sqlite3 property, not a > geopackage property. (And qgis whacks it on every open.) > > If I am following, you are suggesting a geopackage-level property (in > some table?), and a rule that when a reader or write opens the file, if > the sqlite3 setting does not match, the sqlite3 setting is forced and an > error is logged? That's correct, but without the error logging. Nyall ___ 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
Re: [Qgis-user] Readonly gpkg
Nyall Dawson writes: > On Wed, 12 Jan 2022 at 11:00, Greg Troxel wrote: >> >> >> writes: >> >> > A. Ideally WAL/DELETE would be a connection (in the sense of the >> > Browser connections list) level decision changeable by the user, not a >> > layer-level decision and not (as now) an application setting used for >> > all gpkg connections. >> >> Fundamentally WAL/DELETE is a (changeable) property of an sqlite3 file, >> and thus of a geopackage container. So exposing it as a db browser >> property and allowing it to be changed, with a warning that it is a DB >> Admin operation, not a connection operation, seems ok. > > Instead of exposing this as a per-workstation, QGIS only property, I'd > propose instead that the property be set somewhere in the gpkg > metadata itself, so that regardless of which user opens it in a > multi-user environment, they'll always get the property set by the db > administrator. (Maybe this could even become a future formal gpkg > extension...?) Right now, I think WAL/DELETE is a property of the sqlite3 file which contains the geopackage data. But it's a sqlite3 property, not a geopackage property. (And qgis whacks it on every open.) If I am following, you are suggesting a geopackage-level property (in some table?), and a rule that when a reader or write opens the file, if the sqlite3 setting does not match, the sqlite3 setting is forced and an error is logged? If I got that wrong which I probably did, I wonder what you do mean. 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
Re: [Qgis-user] Readonly gpkg
On Wed, 12 Jan 2022 at 11:00, Greg Troxel wrote: > > > writes: > > > A. Ideally WAL/DELETE would be a connection (in the sense of the > > Browser connections list) level decision changeable by the user, not a > > layer-level decision and not (as now) an application setting used for > > all gpkg connections. > > Fundamentally WAL/DELETE is a (changeable) property of an sqlite3 file, > and thus of a geopackage container. So exposing it as a db browser > property and allowing it to be changed, with a warning that it is a DB > Admin operation, not a connection operation, seems ok. Instead of exposing this as a per-workstation, QGIS only property, I'd propose instead that the property be set somewhere in the gpkg metadata itself, so that regardless of which user opens it in a multi-user environment, they'll always get the property set by the db administrator. (Maybe this could even become a future formal gpkg extension...?) Nyall > > > In the distant future, I could see the Browser providing a UI wrapper > > to send the right pragma JOURNAL_MODE if user requests. > > Yes, that makes sense. > > > > > B. Currently, one option would be for me/others to set > > qgis/walForSqlite to false, which would solve the problem for readonly > > gpkgs and only slightly downgrade performance for (modest-sized) > > writeable gpkg primarily edited via the canvas. > > I think so. It would also fix the bug that reading a geopackage layer > causes a write operation to the filesystem. > > However, it would have a bug that if someone had set a geopackage to > WAL, then this would reset it back to DELETE. > > > C. Alternately, continuing to let QGIS use WAL, but setting the > > specific .gpkg file to be read-only in the file system is a harmless > > if crude solution (as long as I have no intention of actually editing > > the layers in that gpkg). > > Probably, but I do want to edit. I just don't want a fs write unless I > actually do so. > ___ > 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 ___ 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
Re: [Qgis-user] Readonly gpkg
writes: > A. Ideally WAL/DELETE would be a connection (in the sense of the > Browser connections list) level decision changeable by the user, not a > layer-level decision and not (as now) an application setting used for > all gpkg connections. Fundamentally WAL/DELETE is a (changeable) property of an sqlite3 file, and thus of a geopackage container. So exposing it as a db browser property and allowing it to be changed, with a warning that it is a DB Admin operation, not a connection operation, seems ok. > In the distant future, I could see the Browser providing a UI wrapper > to send the right pragma JOURNAL_MODE if user requests. Yes, that makes sense. > B. Currently, one option would be for me/others to set > qgis/walForSqlite to false, which would solve the problem for readonly > gpkgs and only slightly downgrade performance for (modest-sized) > writeable gpkg primarily edited via the canvas. I think so. It would also fix the bug that reading a geopackage layer causes a write operation to the filesystem. However, it would have a bug that if someone had set a geopackage to WAL, then this would reset it back to DELETE. > C. Alternately, continuing to let QGIS use WAL, but setting the > specific .gpkg file to be read-only in the file system is a harmless > if crude solution (as long as I have no intention of actually editing > the layers in that gpkg). Probably, but I do want to edit. I just don't want a fs write unless I actually do so. 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
Re: [Qgis-user] Readonly gpkg
Even Rouault writes: > 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/qgsogrprovid erutils.cpp#L1005 Greg Troxel writes: > 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. > 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, ... > 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. You've left me in the dust, which is fine. But it sounds that A. Ideally WAL/DELETE would be a connection (in the sense of the Browser connections list) level decision changeable by the user, not a layer-level decision and not (as now) an application setting used for all gpkg connections. In the distant future, I could see the Browser providing a UI wrapper to send the right pragma JOURNAL_MODE if user requests. B. Currently, one option would be for me/others to set qgis/walForSqlite to false, which would solve the problem for readonly gpkgs and only slightly downgrade performance for (modest-sized) writeable gpkg primarily edited via the canvas. C. Alternately, continuing to let QGIS use WAL, but setting the specific .gpkg file to be read-only in the file system is a harmless if crude solution (as long as I have no intention of actually editing the layers in that gpkg). Do I have that right? Thanks! Martin ___ 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
Re: [Qgis-user] Readonly gpkg
Even Rouault 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. 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
Re: [Qgis-user] Readonly gpkg
Even Rouault 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. 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
Re: [Qgis-user] Readonly gpkg
Le 11/01/2022 à 18:58, Greg Troxel a écrit : Jürgen E. Fischer writes: On Tue, 11. Jan 2022 at 09:10:57 -0500, perg...@gmail.com wrote: I've tried making the gpkg read-only, which hasn't crashed anything in brief testing and generates no warnings. However, it feels risky, and clearly QGIS or OGR aren't fully happy, since the temporary shm and wal files don't get deleted on exit. So I'm hesitant to continue doing this for fear of a crash when I'm actually doing something useful. We do the same with world_map.gpkg - see https://github.com/qgis/QGIS/issues/35383. I had seen that (NetBSD 9, pkgsrc, qgis 3.16.x) and wondered if it was a packaging error I should look into. Now I understand. I have long found it problematic that the wal/shm files exist and persist for long periods, and that gpkd files I don't intend to change are modified at the OS level. It strikes me that the basic bug needs one of two things: 1) The geopackage driver should have a read-only access mode, and qgis should start out that way, trying to change to read/write only when the user hits the edit button on one of the layers. And, if the file is read only, there should be a nice error like "can't make this layer writable" and the toggle to write should fail. 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 With this option, there perhaps needs to be some kind of lock outside of sqlite, which can be multireader and single writer. 2) somehow, the geopackage layer could hide all this and logically open the layer read/write, but refrain from writing to the filesystem. Then, when there is an actual write, the db open-for-write can happen. After the database is written, if no layers [are editable]or[have unwrittend changes] for some period of time (5m?) the database should be changed back to read only. This is sort of like 1 but at the gdal layer vs the qgis layer. Because of this I'm starting to think about moving all my data into postgis, and not use geopackages any more. ___ 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 -- http://www.spatialys.com My software is free, but my time generally not. ___ 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
Re: [Qgis-user] Readonly gpkg
Jürgen E. Fischer writes: > On Tue, 11. Jan 2022 at 09:10:57 -0500, perg...@gmail.com wrote: >> I've tried making the gpkg read-only, which hasn't crashed anything in brief >> testing and generates no warnings. However, it feels risky, and clearly QGIS >> or OGR aren't fully happy, since the temporary shm and wal files don't get >> deleted on exit. So I'm hesitant to continue doing this for fear of a crash >> when I'm actually doing something useful. > > We do the same with world_map.gpkg - see > https://github.com/qgis/QGIS/issues/35383. I had seen that (NetBSD 9, pkgsrc, qgis 3.16.x) and wondered if it was a packaging error I should look into. Now I understand. I have long found it problematic that the wal/shm files exist and persist for long periods, and that gpkd files I don't intend to change are modified at the OS level. It strikes me that the basic bug needs one of two things: 1) The geopackage driver should have a read-only access mode, and qgis should start out that way, trying to change to read/write only when the user hits the edit button on one of the layers. And, if the file is read only, there should be a nice error like "can't make this layer writable" and the toggle to write should fail. With this option, there perhaps needs to be some kind of lock outside of sqlite, which can be multireader and single writer. 2) somehow, the geopackage layer could hide all this and logically open the layer read/write, but refrain from writing to the filesystem. Then, when there is an actual write, the db open-for-write can happen. After the database is written, if no layers [are editable]or[have unwrittend changes] for some period of time (5m?) the database should be changed back to read only. This is sort of like 1 but at the gdal layer vs the qgis layer. Because of this I'm starting to think about moving all my data into postgis, and not use geopackages any more. 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
Re: [Qgis-user] Readonly gpkg
Hi Martin, On Tue, 11. Jan 2022 at 09:10:57 -0500, perg...@gmail.com wrote: > I've tried making the gpkg read-only, which hasn't crashed anything in brief > testing and generates no warnings. However, it feels risky, and clearly QGIS > or OGR aren't fully happy, since the temporary shm and wal files don't get > deleted on exit. So I'm hesitant to continue doing this for fear of a crash > when I'm actually doing something useful. We do the same with world_map.gpkg - see https://github.com/qgis/QGIS/issues/35383. Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50 Software Engineer D-26506 Nordenhttps://www.norbit.de QGIS release manager (PSC) Germany IRC: jef on Libera|OFTC 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
