<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body>
<p>Hi Even,
</p>
<div class="moz-cite-prefix">On 5/8/20 12:30 PM, Even Rouault wrote:<br>
</div>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<meta http-equiv="content-type" content="text/html;
charset=windows-1252">
<meta name="qrichtext" content="1">
<style type="text/css">
p, li { white-space: pre-wrap; }
</style>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> * The fid requirement</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> I sometimes want my features to be identified by uuids or others.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> They also tend to accumulate if derived datasets are created (through</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> processing etc). If I need some pseudo stable primary key there is a</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> rowid builtin into sqlite, we don't need a second one.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Possible mitigation: alter the ogr implementation. possibly alter the</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> standard (required?)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I think the main issue here is that we expose the FID as a normal column in QGIS. This is a QGIS only behaviour, that could be easily removed.</p>
</blockquote>
<p>Can we still turn this road now?</p>
<p>Many will meanwhile depend on this behavior (it's not always bad). So if we treat it as a system attribute and hide it away on provider level we risk breaking workflows. Also I'm unsure about further processing, do we remove these columns from resulting files or not?</p>
<p>Also, will it then be possible to add another "user id column" with autoincrement (I couldn't get sqlite to create a second autoincrement coolumn here in a quick test).</p>
<p>Maybe we need a gpkg extension "fid_is_visible" to make this cleaner? or allow creation of gpkgs out of spec?</p>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Keeping a integer primary key column is indeed a requirement of the standard, and can be useful in circumstances where you run VACUUM on the DB, which would alter rowid in case of deleted features and possibly confuse QGIS identification by feature it if that happened after a layer has been loaded (but that's a bit of a edge scenario).</p>
</blockquote>
I think that's an application level problem to solve and not format
level requirement to impose.<br>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> * The modification on r/o open</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Has caused too much pain on git.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Possible mitigation: a) switch to journal mode=delete (not an easy</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> option because of <a class="moz-txt-link-freetext" href="https://issues.qgis.org/issues/15351">https://issues.qgis.org/issues/15351</a>) </p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> b) only switch</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> to wal mode when layers are put into edit mode (I have strong doubts</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> this is a safe thing to do)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">That should be investigated/tested, but indeed it's not compleletely obvious that a connection that has been opened in the default journal mode=delete will "see" that it has been turned to WAL by another. I believe I looked in the SQLite doc about that scenario, but didn't find anything.</p>
</blockquote>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> * The network share freeze</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Our default file should play nicely with (windows) network shares.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> It's clear to everyone that we can't expect concurrent writes. But it</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> should "just work" for concurrent read by many.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Possible mitigation: switch to journal mode=delete for network shares</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> (we are looking into this)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I don't think journal mode=delete wil prevent SQLite3 from creating locks (or trying to do), to detect potential writes. I'm not sure if that can result in deadlock scenarios if they are network issues. One possibility to avoid all (deal)lock issues is to use the SQLITE_USE_OGR_VFS=YES env variable that will use GDAL I/O layer instead of SQLite3 built-in one. The side effect of this is that as GDAL I/O layer doesn't implement locking, no locking attempt is done. So it has been obvserved in <a class="moz-txt-link-freetext" href="https://github.com/qgis/QGIS/issues/27899#issuecomment-535413602">https://github.com/qgis/QGIS/issues/27899#issuecomment-535413602</a> that it actually resulted in speedups.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Alternate implementation with identical effects&drawbacks: use the SQLite3 uri syntax with the immutable=1 or nolock=1. See <a class="moz-txt-link-freetext" href="https://www.sqlite.org/uri.html">https://www.sqlite.org/uri.html</a> </p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Of course, in such case, edits should be disabled, or enabled only with a big big red warning, since database corruption would occur for sure if 2 people tried to edit the DB simulatenously. I also thing that in a scenario 1 writer, other readers, the readers could possibly see inconsistant/broken state in a transient way. That could actually arise if there's a single machine editing & viewing the database, for example if a rendering thread reads during the time the DB is written.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">(but the same could probably seen with some editing scenarios on shapefiles)</p>
</blockquote>
<p>I think we can accept these constraints. It's our job to do our best to prevent users to shoot themselves in their foot. But not at the expense of preventing valid use cases.</p>
<p>I.e. it should be clear to everyone that a file based format is meant for single write access only. As you said, it's probably the case with shapefiles as well (hmm, I wanted to keep them out of this discussion) and the limitation is obvious to most of us.
</p>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> * The wal file appearing next to the file</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> It is confusing to newcomers and looks almost like a sidecar file. I</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> would care less if it was put into some system cache folder instead of</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> just into my data folder. Or at least if it was a hidden file.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">By overriding sqlite3 I/O callback, I'm wondering if we couldn't move the .wal file somewhere else in the filesystem (of course that would only work for OGR enabled consumer, but probably good enough). That said, the modification of the first 16 bytes of the main .gpkg file, which cause issue for file synchronization, would remain, as it is a design constraint of WAL.</p>
</blockquote>
Intriguing idea, hmmm...<br>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Possible mitigation: switch to journal mode=delete (not an easy</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> option because of <a class="moz-txt-link-freetext" href="https://issues.qgis.org/issues/15351">https://issues.qgis.org/issues/15351</a>)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I was wondering if we could consult the SQLite3 main author (<a class="moz-txt-link-freetext" href="http://www.hwaci.com/drh/">http://www.hwaci.com/drh/</a>) on all those locking and concurrency issues, as he has probably experience with similar scenarios.</p>
</blockquote>
<p>I'm sure we are not the first ones to hit this. And having someone with experience and deeper understanding involved sounds very good.</p>
<p>Does someone have a contact there?</p>
<p>(I also noted the sqlite consortium, but the pricing there sounds like it's out of league for the qgis project.)
</p>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> *The requirement for a single geometry column per table</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> I just don't see a good reason to forbid that</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Possible mitigation: a) alter the standard. b) ignore the standard</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> and patch the ogr implementation.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I don't think the Geopackage OGC SWG would be keen to change that. I believe I floated the idea around a few years ago, but they wanted GeoPackage core to remain simple and adding multiple geometry columns goes against that.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">That said, GeoPackage has an extension mechanism, and it would be possible on the OGR side to define one to flag tables with multiple geometry columns as extended (I've just verified for exemple that the definition of the gpkg_geometry_columns system table has a unique constraint on the tuple (table_name, column_name), and not just table_name, so this is a provision for that extension). The OGR Spatialite driver handles several geometry columns fine, no reason this couldn't be done on GPKG side too.</p>
</blockquote>
<p>Good to know we have a possibility her. I have also heard similar desires from other software manufacturers, I guess this extension will be implemented in a couple of applications.</p>
<p>Matthias
</p>
<blockquote type="cite" cite="mid:2388211.47gt7cWF4j@even-i700">
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Even</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">-- </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Spatialys - Geospatial professional services</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><a class="moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a></p>
</blockquote>
</body>
</html>