[QGIS-Developer] Geopackage: ERROR: Too many connections: max 64 ?

[Even Rouault]

On lundi 2 octobre 2017 14:55:46 CEST Richard Duivenvoorde wrote:
> Hi Devs,
> 
> I created a geopackage from 144 individual (hourly data) shapefiles as
> individual tables (in one gpkg).
> 
> To load all those tables in QGIS I can 'load vector data', point to the
> gpkg, choose 'select all' and then OK.
> 
> BUT: it looks like both in 2.18 and in master it for every table QGIS
> (or OGR?) creates a connection?
> In my Debug builds the loading takes several minutes showing me msg
> like:
> 
> src/providers/ogr/qgsogrprovider.cpp: 3196: (OGRDestroyWrapper) [167ms]
> GPKG: Trying to return to delete mode
> src/providers/ogr/qgsogrprovider.cpp: 3214: (OGRDestroyWrapper) [0ms]
> Return: In ExecuteSQL(): sqlite3_step(PRAGMA journal_mode = delete):
>    database is locked
> src/providers/ogr/qgsogrprovider.cpp: 3224: (OGRDestroyWrapper) [3ms]
> GPKG: Trying again
> ERROR: Too many connections: max 64

Hi Richard,

After digging a bit, i found that this error comes from libspatialite. The reason is that the 
OGR GPKG driver enables spatialite for each OGR GeoPackage datasource, so as to benefit 
from spatialite functions. When you activate spatialite for a sqlite connection, you have to 
create a "spatialite context", and apparently there is a hardcoded (well a #define constants) 
limitation to 64 in spatialite.

The core of the problem is that for each geopackage layer added in your QGIS project, you 
instanciate a QgsOgrProvider object which opens a OGR datasource object, which opens a 
spatialite connection and sqlite3 resources as well. So this doesn't scale very well. Even if 
spatialite has not that limitation, at some point you would hit the default limitation of 1024 
open file descriptors on Linux (you would hit it for approximatively (1024 - some_constant) / 
3 ~= 300 layers, since in wal mode, you need to have 3 handles opened)

It would be better if that each QgsOgrProvider that refers to the same OGR datasource could 
effectively share the same OGR datasource object. But there would be issues in multi-
threaded contexts in doing so in a naive way, so (all) OGR calls to a shared datasource should 
be protected with a mutex.

So this is probably actionable, but not a 5 minute fix.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20171002/9994e696/attachment-0001.html>