[QGIS-Developer] Max connection PostgreSQL limit

[Francisco Puga]

Thanks a lot Mathias,

It helps a lot. I didn't find anything weird about this, and it seems to
work correctly.

After a bit of research I think it is a database/networking issue. The
database is in a cloud provider and it seems that when the connection has
no activity for some time, it becomes unresponsive, but remains idle in
postgresql.

I'm playing with tcp_keepalives_* postgres settings, as the database is <14
and idle_session_timeout is not present. I'm monitoring it to see if it
works.

Again, thank you.



El jue, 6 oct 2022 a las 12:16, Matthias Kuhn (<matthias at opengis.ch>)
escribió:

> 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.
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20221007/db539979/attachment.htm>