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 (<matth...@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@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. >> >
_______________________________________________ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
