QGIS
QGIS copied to clipboard
qgis
QGIS 3.32 reserves ridiculous number of idle connections on PostgreSQL 11.12
What is the bug or the crash?
There seems to be an issue with QGIS 3.32 when connecting to large projects that have data stored on PostgresSQL (tested version 11.12). QGIS reserves a very large number of idle connections, making it impossible to load even an entire project, as if it made a separate connection for each individual layer instead of loading them all in one go.
Steps to reproduce the issue
When trying to load projects that work just fine in 3.30 or older versions of QGIS, in larger projects a connection error will appear saying that there are too many connections. The default for PostgreSQL seems to be 100. We've never reached 100 before.
Below you can see the connection activity chart. It begins with a large program loaded in 3.32, then a smaller project loaded in 3.32, another large project in 3.32, a period of pause while 3.28 loaded and then a tiny imperceptible bump in connections when the largest project was loaded in 3.28.
Loading individual layers form the Data Source manager does not seem to cause the same issue, the number of connections being normal.
Versions
QGIS version 3.32.0-Lima QGIS code revision 311a8cb8a6 Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.7.0 PROJ version 9.2.1 EPSG Registry database version v10.088 (2023-05-13) GEOS version 3.11.2-CAPI-1.17.2 SQLite version 3.41.1 PDAL version 2.5.3 PostgreSQL client version 15.2 SpatiaLite version 5.0.1 QWT version 6.1.6 QScintilla2 version 2.13.1 OS version Windows 10 Version 2009
Active Python plugins azimuth_distance_measurement 0.4 contour 2.0.12 coordinates_converter 1.0 deactivate_active_labels 0.5 FreehandRasterGeoreferencer 0.8.3 GeometryShapes 0.7 GroupPointsWithinDistance 0.0.2 HCMGIS 23.2.1 inspireatomclient 0.8.1 joinmultiplelines Version 0.4.1 kmltools 3.1.29 latlontools 3.6.10 pickLayer 3.9.4.post0 postgisQueryBuilder 2.0.1 profiletool 4.2.6 qfieldsync v4.5.0 Qgis2threejs 2.7.1 qgis_hats 1.8 SelectWithin 0.4 StreetView 3.2 turbo_volume 0.1 volume_calculation_tool 0.4 wnt 1.3.6 db_manager 0.1.20 grassprovider 2.12.99 MetaSearch 0.3.6 otbprovider 2.12.99 processing 2.12.99
Supported QGIS version
- [X] I'm running a supported QGIS version according to the roadmap.
New profile
- [x] I tried with a new QGIS profile
Additional context
No response
You can deactivate this function #53069
unfortunately, it can generate a large number of attempts to connect to the database
@MorriganR Thanks. I was looking in the options to see if something like this was implemented, but I didn't know what it would be called. It's only usable if we'd increase the maximum connections to about 1000 on the database, or if connection would be closed after the layer was retrieved, I think it may not be doing this properly at the moment.
Seeing the same behaviour on PG15 with QGIS 3.32 (Windows). Similarly have the PG default 100 connection limit as well. On a project with many layers QGIS ends up throwing error [FATAL: sorry, too many clients already] which also prevents anyone else connecting to Postgres. No issues in QGIS 3.30.
We have our PG max connections set at 1000 so should be ok. However (and this may be related), we're seeing an excessive number of SQL queries on each layer. For example, checking PostGIS version (even if all layers are from same db). See: https://github.com/qgis/QGIS/pull/53112#issuecomment-1611800655
@unacomn do the number of connections remain after the project had loaded/is open? Or is the issue only noticeable during project load?
@weca-theo
According to Dbeaver, the connections are idle, not actively used, but they are still counted as connections towards the maximum number allowed by PostgreSQL while the project is opened, blocking other connections beyond the maximum. They are terminated only after the project is closed.
The picture below is from loading a "small" project in QGIS 3.32
@weca-theo According to Dbeaver, the connections are idle, not actively used, but they are still counted as connections towards the maximum number allowed by PostgreSQL while the project is opened, blocking other connections beyond the maximum. They are terminated only after the project is closed. The picture below is from loading a "small" project in QGIS 3.32
Ok interesting. How many unique PostgreSQL layers are contained within this 'small' project? And same question for your 'large' project. (would be interesting to know how many connections a single PG layer takes up in QGIS!).
@weca-theo A large project is about 105 geometry layers stored in the database (about 10 more are duplicates with different filtering and symbology) and about 30 dictionaries in the same database. There's also about 40 other shapefile or geopackage layers. A "small" one is around 60-70 layers. I think with the parallel loading process uses 1 connection per layer. With it disabled in the Advanced Features, the loading is normal and no extra connections are generated or maintained while the project is open.
Parallel loading would be a neat feature, I guess, but the way it's currently set up, were we to use it in my use case, we'd be hitting the database with over 1000 connections at the same time. (multiple projects loaded on multiple machines from the same database)
@unacomn and @weca-theo , this should be fixed in 3.32.1 available version. Can you confirm?
@vcloarec I can confirm that the fix works in 3.32.1. With parallel layer loading active the number of database connections remains small:
Things are back to normal, which is awesome. Thank you.
I also tried to see if it helped improve performance on a different issue (#51233 extremely slow loading of projects with many print layouts that contain legends), but no dice. Hopefully that issue will get fixed as well some day
@unacomn and @weca-theo , this should be fixed in 3.32.1 available version. Can you confirm?
Hi, this particular issue (connection count bloat) wasn't a direct issue for me, but I'll test loading up my project file in 3.32.1 to see if any wider performance improvement.
update: no noticeable project load time difference between 3.32.0 and 3.32.1, tested on same machine, same .qgz file, same time of day (back to back).