Bug report #9357
Lots of PostGIS connections with a standalone python script
|Affected QGIS version:||2.0.1||Regression?:||No|
|Operating System:||Windows||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:||not reproducable|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||17957|
I have a standalone python script that creates a bunch of PDFs for me.
Recently this script has developed an issue. It uses QGIS-dev so it could be related to some of the changes in master recently as it works fine with QGIS 2.0.
It makes about 42 PDFs then it stops.
It is not actually crashing as such, as you can see it running in task manager. It stays running using between 0 and 2% processor, and fluctuates a little with memory use (around 108840kB). But it does not produce any more PDFs. It is like it is stuck.
From the log file that my script creates, I know that it is hanging with this line of code:
vlayer = QgsVectorLayer(uri.uri(), layerName, "postgres")
If I try and connect to the database using DB Manager in QGIS or pgAdmin III while the script is still running, I get an error saying there is already too many clients already. If I kill the process in windows task manager, I am then able to connect to the database using DB manager.
Has anyone any suggestions as to what is wrong here? Is this a bug?
I updated QGIS dev this morning using the OSGeo4W installer (its revision is eb9ce3a).
As per Tim's response on the qgis-developer mailing list, I added code to remove my PostGIS layers from the MapLayerRegistry when I was finished with them, but this has made no difference, and I didn't need to do this using QGIS 2.0.
#2 Updated by James Stott about 6 years ago
I decided to take a look at the connections that were being sent to PostGIS while my script was running using this SQL:
SELECT query FROM pg_stat_activity;
I get three standard lines from Postgres:
"SELECT count(*) FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_proc'::regclass AND attname = 'proargdefaults'"
"WITH configs AS (SELECT rolname, unnest(setconfig) AS config FROM pg_db_role_setting s LEFT JOIN pg_roles r ON r.oid=s.setrole WHERE s.setdatabase=24576) SELECT rolname, split_part(config, '=', 1) AS variable, replace(config, split_part(config, '=', (...)"
"SELECT query FROM pg_stat_activity;"
When the script is running with QGIS 2.0 I two lines that seem to be coming from my script. They are:
"SELECT styleQML FROM layer_styles WHERE f_table_catalog='rog_data' AND f_table_schema='' AND f_table_name='(Select int4(row_number() OVER ()) AS id, table.knrgnrbnr, ST_INTERSECTION(table.geom, rog_ar5_sol.geom) As geom FROM (SELECT knrgnrbnr, (...)"
I never seem to get any more lines than this while the script is running and these disappear each time the loop iterates and I get a new line. By the end of the script I am left with just the first 3 lines.
With Master, I get the
Close qgisf0 3 line, followed by the line that starts
SELECT styleQML…, but I get 5 of these lines for each loop in my script. And they do not disappear. They just keep adding to the list. When it gets to 100 lines, the script hangs.
#3 Updated by James Stott about 6 years ago
I have included two screenshots to help explain the problem I am having. These screenshots were taken at the same point in my script.
As you can see with 2.0, I only ever see a small number of connections, but with 2.2 they just keep growing until they reach 100, and then the script hangs. I am closing my layers as I go in my script, so for some reason these connections are not closing once I am finished with the layers.
#4 Updated by James Stott about 6 years ago
Have found this to be a good workaround:
vlayer = QgsVectorLayer(uri.uri(), layerName, "postgres", False)
specifying False for the loadDefaultStyleFlag parameter.
This parameter stops QGIS from checking if there are styles in the database, and thus means that it doesn’t run loads of queries that it then doesn’t close.
QGIS still needs to unload some of the connections that it creates when searching for styles when loading query layers (if you don’t specify the False for the loadDefaultStyleFlag), so I believe the bug is still valid, but this is a workaround for me so that is good. I also wonder if True is the right default for the loadDefaultStyleFlag parameter?