[gdal-dev] Changing many features in a GPKG in a transaction, why is the index updated?

[Even Rouault]

Hi Nyall,

What you observe is fully expected. The spatial index must be kept fully 
in sync with the changes in the feature table, even when run within a 
transaction. Otherwise if during the transaction you would execute a 
spatial query, you would get inconsistents result if the spatial index 
wasn't updated. And even if doing only updates, I don't think there 
would really be a way to optimize updating the RTree for several 
geometries at once, at least not with what is available now in SQLite. 
When creating a new table and inserting features, we have an 
optimization to delay (actually build in parallel) the spatial index, 
but such an optimization is really restricted to that particular use 
case. Updates are more complicated.

But... as you mention "bulk attribute changes", assuming you don't 
change geometries, you would be better using OGR_L_UpdateFeature() where 
you could specify to update only a subset of attributes instead of 
updating the whole feature. In that case, the RTree triggers would not 
run. That said even if the update RTree trigger runs, it checks that the 
geometry hasn't changed, so as you mention rtreeUpdate running, I 
suspect you change the geometries as well, in which case I have no 
suggestion.

Even

Le 12/06/2024 à 03:30, Nyall Dawson via gdal-dev a écrit :
> Hi list,
>
> I'm trying to understand some unexpected behavior I'm seeing while 
> profiling bulk attribute changes in a GPKG file.
>
> First, I start a transaction on the layer using 
> OGR_L_StartTransaction. This is successful.
> I then make many calls to OGR_L_SetFeature, changing different 
> features, before finalizing the transaction with OGR_L_CommitTransaction.
>
> Now, when profiling this code, I'm seeing that 
> OGRGeoPackageTableLayer::ISetFeature(OGRFeature*) is taking a very 
> large chunk of time. This is due to the call to sqlite3_step at 
> https://github.com/OSGeo/gdal/blob/2b94d7a274bfeb881a176016de932ba8acd1b79a/ogr/ogrsf_frmts/gpkg/ogrgeopackagetablelayer.cpp#L3134 
> triggering a call to rtreeUpdate within sqlite3.
>
> I'm confused as to why this is happening. If I'm in a transaction, 
> shouldn't the index update be deferred until the transaction is 
> committed? Why would it be updating after every individual update?
>
> Hoping someone can explain what I'm missing here...
> Nyall
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev

-- 
http://www.spatialys.com
My software is free, but my time generally not.