[QGIS-Developer] Max connection PostgreSQL limit

[Matthias Kuhn]

Hi Francisco,

QGIS does internally have connection pooling and uses up to 8 connections
per database.
It detects common database connections based on the URI, where there may be
different parameters used for layers even if they come from the same
database (e.g. SSL parameters, authentication, ...). That could be an
explanation for multiplication of connections.
You can inspect those with:

for _, l in QgsProject.instance().mapLayers().items():
    print(l.dataProvider().uri())

Cheers
Matthias


On Thu, Oct 6, 2022 at 11:13 AM Francisco Puga via QGIS-Developer <
qgis-developer at lists.osgeo.org> wrote:

> Hi,
>
> I'm hitting the default (100) `max_connection` PostgreSQL limit with ~4
> users using a QGIS project with ~25 layers/tables. I know that I can raise
> the limit, but I'm trying to understand how QGIS handles database
> connections.
>
> I didn't find so much information about it:
>
>
>    1.
>    https://lists.osgeo.org/pipermail/qgis-developer/2008-March/003446.html.
>    Old post against, something like, an internal connection pool in QGIS
>    2. https://github.com/qgis/QGIS/issues/30373. Feature request to share
>    a single connection between different layers
>    3. https://stackoverflow.com/a/71557537/930271 an ¿explanation? of how
>    connection works in QGIS. I copy&paste here because this post may be hide
>    for some users:
>
> for what I know every single layer creates at least one, possibly two,
>> connections (one is read only the other one is read-write ), these two
>> connections have the same lifetime of the layer, the read-write connection
>> is created on-demand if a read-write operation is requested
>> (editing/adding/deleting features).
>
> Multiple layers using the same DB connection will not share these two
>> connections.
>
> In addition to the two connections above, temporary connections are
>> created from a connection pool when the features are fetched from the
>> layer(s), these connections are pooled (shared) and released when the
>> fetching is finished, there is also a timeout to close them after 60
>> seconds idle.
>
> It would be useful to check PG logs and see in which state these
>> connections are, from my tests, the read-only connection is kept in
>> ClientRead | idle.
>
>
> So,
>
>    - Can someone confirm the previous quote, explain how QGIS handles
>    connections, or point me to relevant code or doc?
>    - An invisible/unchecked layer consumes a connection?
>    - Does make sense a feature request about this topic. Maybe
>       - Reuse connection
>       - a kind of internal pooling
>       - a configurable user limit, so next database access will wait for
>       other connections to close to start operations
>       - ...
>
> Many thanks.
>
> --
> Francisco Puga
> iCarto | Innovación, Cooperación, Cartografía y Territorio S.L.
> https://icarto.es
>
> _______________________________________________
> QGIS-Developer mailing list
> QGIS-Developer at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>

-- 
 <https://qfield.org/get/>

QFIELD 2.0 IS HERE! - Hold the power of QGIS in 
your hand - learn more 
<https://www.opengis.ch/2022/04/05/qfield-2-0-is-here/> - get it now 
<https://qfield.org/get>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20221006/03d0ce83/attachment.htm>