Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
I too am struggling with how to use geopackages with a team. And am wondering if anyone has figured out how to keep them from being marked as changed when opened for viewing in QGIS. This is fine with small geopackages but with large ones it uses up a lot of bandwidth. QSettings().setValue("/qgis/walForSqlite3", False) makes it not enter WAL mode but it still is marked as changed. Changing the environment variable OGR_SQLITE_JOURNAL = DELETE also turns off WAL mode but still marks the file as changes. We use google file stream and it is not possible to mark the file as read only in google file steam so that is not an option. I guess my question is are geopackages just incompatible with any sort of syncing service- google drive, drive file stream, dropbox, one drive, etc? If that is the case I think that could be a severe limiting factor for the format as these file syncing services are ubiquitous at this point and are likely to increase in the increasingly work from home environment that we are facing. In my naivety I image a system that does not mark the file as changed when loaded but when any of the layers are toggled for editing it creates a lock file that prevents other users from editing the file as MS Word does. After the layer is saved and editing is toggled off the lock file disappears (is this what the WAL file si doing?). I know there could still be issues with network latency but if google decided that there is a conflict and it creates a duplicate file. Please don't make me go back to shpfls! :) -Bas -- Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
> Hi Borys, > > Are you sure that this is also a problem with 2.x? > > If I remember correctly 2.x browser panel does not open gpkg for scanning > contents, this feature was added for 3.x. Ok, the problem with 2.18 is a bit different. It is limited to concurrent use of two QGIS instances, and only occurs for some gpkgs: 1. Run QGIS 2.18, go to the Browser and expand a directory containing a gpkg. 2. Run another QGIS instance and load any layer from that gpkg. 3. The loop begins: the wal and shm files are repeatedly created and removed from the disk, and the expanded branch is being refreshed. Your fixes of course didn't help this particular case. I don't observe it in QGIS 3, so I'm leaving it as it was. Furthermore, I didn't manage to reproduce it with every gpkg. Regards, Borys ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
Me wrote: > Bingo! Seems it's exactly the 2.18 issue. I'll backport it this afternoon > (unless you guys are faster) and ensure it helps. Ooops, not today, as I just realized I don't have GDAL 2 in my ld QGIS2 dev environment (and Qt4 in my current one). I'll sort it out before the 2.18.19 release. Borys ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
On mardi 10 avril 2018 08:11:30 CEST Richard Duivenvoorde wrote: > On 09-04-18 22:22, Even Rouault wrote: > > Or perhaps have some option in a dialog box somewhere! "I promise that I > > will not edit this file in any concurrent session, or in this session, so > > you can safely open it in DELETE mode" ? Or spend more time digging again > > into the details of sqlite locking to see if we could make a clever use > > of it, and/or possibly improving it in sqlite itself (rather involved!) > > I would be ok with an option in the dialog, though it is hard to promise > that somebody *else* will not open it too in a non readonly mode, in a > networked work environment ... You'll have issues with sqlite in a networking environment, as WAL cannot work in that context (QGIS does some effort to detect if the file location is a network driver or not, before deciding to turn WAL on). So you'll get the default journaling behaviour, which is lock prone. > > Is it a possibility (at least on linux) to look at the file permissions? > That would open the possibility to put a read-only (permissioned) file, > and in that way you would promise to not edit/delete? Just tested: QGIS can open file a read-only .gpkg on Linux (it will fallback to the default journaling mode), and presumably on Windows as well. Or were you talking about some side car files that would be used to application level locking ? -- Spatialys - Geospatial professional services http://www.spatialys.com ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
Thanks a lot! Dnia wtorek, 10 kwietnia 2018 09:01:56 CEST Alessandro Pasotti pisze: > See: > > https://github.com/qgis/QGIS/commit/ 1d4c6061a8a5512092041aafea14a8a32247d046 > #diff-e89106528fe0226767dfccab355e44dc > > but I'm afraid this wasn't all of it. Bingo! Seems it's exactly the 2.18 issue. I'll backport it this afternoon (unless you guys are faster) and ensure it helps. Regards, Borys ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze: > Hi, I also faced this problem when Implementing gpkg support in the QGIS > browser panel: when the file browser scanned the folder, it opened the > gpkg, which in turn changed its file stats and made the directory watcher > think that something has changed in the directory, which in turns started > scanning again in a delicious endless loop :) > > I think I solved this with a kind of locker for the directory scan thread, > but it was't easy. Ah so this it the loop in 2.18... Could you recall that commit that fixed it in 3.0? I understand it's not trivial to backport it to 2.18? Regards, Borys ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
> Hi Borys, > > Are you sure that this is also a problem with 2.x? > > If I remember correctly 2.x browser panel does not open gpkg for scanning > contents, this feature was added for 3.x. It doesn't show the GPKG contents, but it starts the loop (sometimes?) when scanning a directory containing a GPKG. Now I recall also a rush in the system file browser - like the wal and shm files were repeatedly removed and created back. I don't remember with which QGIS version and which OS I observed it, but I'll try to reproduce it. Regards, Borys PS. Shouldn't we move to the developer ML? :) ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
See: https://github.com/qgis/QGIS/commit/1d4c6061a8a5512092041aafea14a8a32247d046#diff-e89106528fe0226767dfccab355e44dc but I'm afraid this wasn't all of it. See also: https://github.com/qgis/QGIS/commit/bcb48f2bdc8f274cbc68f376489430f45e0b17d1#diff-e89106528fe0226767dfccab355e44dc On Tue, Apr 10, 2018 at 8:55 AM, Nyall Dawson wrote: > On 10 April 2018 at 16:47, Alessandro Pasotti wrote: > > On Tue, Apr 10, 2018 at 8:33 AM, Borys Jurgiel > > wrote: > >> > >> Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze: > >> > >> > >> > >> > Hi, I also faced this problem when Implementing gpkg support in the > QGIS > >> > >> > browser panel: when the file browser scanned the folder, it opened the > >> > >> > gpkg, which in turn changed its file stats and made the directory > >> > watcher > >> > >> > think that something has changed in the directory, which in turns > >> > started > >> > >> > scanning again in a delicious endless loop :) > >> > >> > > >> > >> > I think I solved this with a kind of locker for the directory scan > >> > thread, > >> > >> > but it was't easy. > >> > >> > >> > >> Ah so this it the loop in 2.18... Could you recall that commit that > fixed > >> it in 3.0? I understand it's not trivial to backport it to 2.18? > > > > > > Hi Borys, > > > > Are you sure that this is also a problem with 2.x? > > > > If I remember correctly 2.x browser panel does not open gpkg for scanning > > contents, this feature was added for 3.x. > > I've seen this with recent versions of 2.18 > > Nyall > -- Alessandro Pasotti w3: www.itopen.it ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
On 10 April 2018 at 16:47, Alessandro Pasotti wrote: > On Tue, Apr 10, 2018 at 8:33 AM, Borys Jurgiel > wrote: >> >> Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze: >> >> >> >> > Hi, I also faced this problem when Implementing gpkg support in the QGIS >> >> > browser panel: when the file browser scanned the folder, it opened the >> >> > gpkg, which in turn changed its file stats and made the directory >> > watcher >> >> > think that something has changed in the directory, which in turns >> > started >> >> > scanning again in a delicious endless loop :) >> >> > >> >> > I think I solved this with a kind of locker for the directory scan >> > thread, >> >> > but it was't easy. >> >> >> >> Ah so this it the loop in 2.18... Could you recall that commit that fixed >> it in 3.0? I understand it's not trivial to backport it to 2.18? > > > Hi Borys, > > Are you sure that this is also a problem with 2.x? > > If I remember correctly 2.x browser panel does not open gpkg for scanning > contents, this feature was added for 3.x. I've seen this with recent versions of 2.18 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
On Tue, Apr 10, 2018 at 8:33 AM, Borys Jurgiel wrote: > Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze: > > > > > Hi, I also faced this problem when Implementing gpkg support in the QGIS > > > browser panel: when the file browser scanned the folder, it opened the > > > gpkg, which in turn changed its file stats and made the directory watcher > > > think that something has changed in the directory, which in turns started > > > scanning again in a delicious endless loop :) > > > > > > I think I solved this with a kind of locker for the directory scan > thread, > > > but it was't easy. > > > > Ah so this it the loop in 2.18... Could you recall that commit that fixed > it in 3.0? I understand it's not trivial to backport it to 2.18? > Hi Borys, Are you sure that this is also a problem with 2.x? If I remember correctly 2.x browser panel does not open gpkg for scanning contents, this feature was added for 3.x. -- Alessandro Pasotti w3: www.itopen.it ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
On Tue, Apr 10, 2018 at 12:07 AM, rjwillson wrote: > Thanks Even, > > It definitely doesn't sound like an easy solution. > > I wonder how others deal with it and maybe someone in the developers list > will have some ideas > > I have just been applying band aid fixes for the last year to prevent the > gpkgs from updating themselves but yesterday I discovered the beauty of > storing rasters in the gpkg format. Unfortunately the rasters make the > gpkgs large and so I can't have them just start uploading on a field tablet > via cell connection when they get viewed in Q or other app like Roam. > > Hi, I also faced this problem when Implementing gpkg support in the QGIS browser panel: when the file browser scanned the folder, it opened the gpkg, which in turn changed its file stats and made the directory watcher think that something has changed in the directory, which in turns started scanning again in a delicious endless loop :) I think I solved this with a kind of locker for the directory scan thread, but it was't easy. I feel that it would be really useful is we could open a gpkg from GDAL in read-only mode without creating the WAL, expecially without creating the WAL in the same directory (but I guess this is by design, buried deep into sqlite code). -- Alessandro Pasotti w3: www.itopen.it ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
On 09-04-18 22:22, Even Rouault wrote: > Or perhaps have some option in a dialog box somewhere! "I promise that I will > not edit this file in any concurrent session, or in this session, so you can > safely open it in DELETE mode" ? Or spend more time digging again into the > details of sqlite locking to see if we could make a clever use of it, and/or > possibly improving it in sqlite itself (rather involved!) I would be ok with an option in the dialog, though it is hard to promise that somebody *else* will not open it too in a non readonly mode, in a networked work environment ... Is it a possibility (at least on linux) to look at the file permissions? That would open the possibility to put a read-only (permissioned) file, and in that way you would promise to not edit/delete? There have been other issues also with the wal-files (eg browser was also rescanning dirs etc), so it would be cool to come up with / invest in a good solution? Regards, Richard ___ 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] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
Thanks Even, It definitely doesn't sound like an easy solution. I wonder how others deal with it and maybe someone in the developers list will have some ideas I have just been applying band aid fixes for the last year to prevent the gpkgs from updating themselves but yesterday I discovered the beauty of storing rasters in the gpkg format. Unfortunately the rasters make the gpkgs large and so I can't have them just start uploading on a field tablet via cell connection when they get viewed in Q or other app like Roam. I have been trying to get off shapefiles for 15 years now because of their limitations but I find I have to keep going back to them Rob Original message From: Even Rouault Date: 2018-04-09 16:22 (GMT-05:00) To: qgis-user@lists.osgeo.org Cc: rjwill...@gmail.com Subject: Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited On lundi 9 avril 2018 15:24:41 CEST rjwill...@gmail.com wrote: > I would really like to use and promote GeoPackage as the go to data format > in my organization but I am having the following problem that I haven't been > able to solve. > > > > When ever someone opens a Q template project that has a layer with a > GeoPackage data source, the GeoPackage file (not its contents) will be > altered and it will show that it has been updated in the Windows file > manager. I have been told that this happens because the write ahead logging > has been triggered (creation of *.wal and *.shm) files by simply viewing the > contents of the GeoPackage in QGIS and/or in Intramaps Roam (works on top > of QGIS for tablets). > > > > This is problematic because most users in our organization will then look at > the windows modified date time stamp and conclude that the file's contents > have been modified. Additionally, all of the GIS layers are stored in > Dropbox and subsequently Dropbox concludes that the file has been modified > and it re-uploads a new copy of the GeoPackage. When it is a large > GeoPackage > 1 GB in size this is quite problematic and causes a cascade of > uploads and downloads. > > > > Over the last few months I have tried to suppress this modification for the > large GeoPackages in our system in the following ways: (1) make the layer > read only in the QGIS project (no effect); (2) make the Dropbox folder read > only for all users except the Dropbox administrator (this is effective for > the large base layers); (3) change the properties of the file in windows to > read only (effective for the Dropbox administrator); and (4) add a user > variable to the windows system environment variables: OGR_SQLITE_JOURNAL = > DELETE (effective for QGIS but not for Intramaps Roam) > > > > Combining 2, 3, and 4 allows me to use GeoPackage for layers I can ensure > are placed in the read-only Dropbox folders. > > > > It is likely that my organization will never need the simultaneous editing > of datasets that is facilitated by Write-Ahead Logging, but I would really > like to be able to replace shapefiles and File geodatabases with GeoPackages > in all of our projects going forward. > > > > Therefore, is there a simpler way to suppress the write ahead logging > behaviour that causes the files to believe they have been updated for all > GeoPackages created in QGIS or used in a QGIS project? My memory is already fading about the details, but the reason for the current behaviour is that if you open a GeoPackage database in read-only non-WAL mode and someone else try to edit it concurrently, unpleasant locking will occur. Actually due to how QGIS works, locking could occur even in the same session (since background rendering threads can open read-only connections while the main connection is writing data). See https://issues.qgis.org/issues/15351 So the solution was: commit b6b8759efbeb833d0d3dbf6df008087701361ad3 Author: Even Rouault Date: Thu Sep 22 23:23:00 2016 +0200 Fix database locking when editing GeoPackage Concurrent read and write can lock a GeoPackage database given the default journaling mode of SQLite (delete). Use WAL when possible to avoid that. Fixes #15351 I understand your use case as well, but I don't have a solution in mind that would fit all. I don't remember if I tried scenarios where one connection would open in DELETE mode and another one would try to turn to WAL on. A quick try shows that the a connection that opens in DELETE mode doesn't see the change to WAL done by another one. And WAL documentation doesn't describe this scenario, so it is quite unknown what would happen. So the current behaviour is to be on the safe side and always turn on WAL on opening. Or perhaps have some option in a
Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited
On lundi 9 avril 2018 15:24:41 CEST rjwill...@gmail.com wrote: > I would really like to use and promote GeoPackage as the go to data format > in my organization but I am having the following problem that I haven't been > able to solve. > > > > When ever someone opens a Q template project that has a layer with a > GeoPackage data source, the GeoPackage file (not its contents) will be > altered and it will show that it has been updated in the Windows file > manager. I have been told that this happens because the write ahead logging > has been triggered (creation of *.wal and *.shm) files by simply viewing the > contents of the GeoPackage in QGIS and/or in Intramaps Roam (works on top > of QGIS for tablets). > > > > This is problematic because most users in our organization will then look at > the windows modified date time stamp and conclude that the file's contents > have been modified. Additionally, all of the GIS layers are stored in > Dropbox and subsequently Dropbox concludes that the file has been modified > and it re-uploads a new copy of the GeoPackage. When it is a large > GeoPackage > 1 GB in size this is quite problematic and causes a cascade of > uploads and downloads. > > > > Over the last few months I have tried to suppress this modification for the > large GeoPackages in our system in the following ways: (1) make the layer > read only in the QGIS project (no effect); (2) make the Dropbox folder read > only for all users except the Dropbox administrator (this is effective for > the large base layers); (3) change the properties of the file in windows to > read only (effective for the Dropbox administrator); and (4) add a user > variable to the windows system environment variables: OGR_SQLITE_JOURNAL = > DELETE (effective for QGIS but not for Intramaps Roam) > > > > Combining 2, 3, and 4 allows me to use GeoPackage for layers I can ensure > are placed in the read-only Dropbox folders. > > > > It is likely that my organization will never need the simultaneous editing > of datasets that is facilitated by Write-Ahead Logging, but I would really > like to be able to replace shapefiles and File geodatabases with GeoPackages > in all of our projects going forward. > > > > Therefore, is there a simpler way to suppress the write ahead logging > behaviour that causes the files to believe they have been updated for all > GeoPackages created in QGIS or used in a QGIS project? My memory is already fading about the details, but the reason for the current behaviour is that if you open a GeoPackage database in read-only non-WAL mode and someone else try to edit it concurrently, unpleasant locking will occur. Actually due to how QGIS works, locking could occur even in the same session (since background rendering threads can open read-only connections while the main connection is writing data). See https://issues.qgis.org/issues/15351 So the solution was: commit b6b8759efbeb833d0d3dbf6df008087701361ad3 Author: Even Rouault Date: Thu Sep 22 23:23:00 2016 +0200 Fix database locking when editing GeoPackage Concurrent read and write can lock a GeoPackage database given the default journaling mode of SQLite (delete). Use WAL when possible to avoid that. Fixes #15351 I understand your use case as well, but I don't have a solution in mind that would fit all. I don't remember if I tried scenarios where one connection would open in DELETE mode and another one would try to turn to WAL on. A quick try shows that the a connection that opens in DELETE mode doesn't see the change to WAL done by another one. And WAL documentation doesn't describe this scenario, so it is quite unknown what would happen. So the current behaviour is to be on the safe side and always turn on WAL on opening. Or perhaps have some option in a dialog box somewhere! "I promise that I will not edit this file in any concurrent session, or in this session, so you can safely open it in DELETE mode" ? Or spend more time digging again into the details of sqlite locking to see if we could make a clever use of it, and/or possibly improving it in sqlite itself (rather involved!) Even -- Spatialys - Geospatial professional services http://www.spatialys.com ___ 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