Bug report #9357

Lots of PostGIS connections with a standalone python script

Added by James Stott almost 11 years ago. Updated over 6 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
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

Description

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.

issue_v2_0.jpg - PgAdmin view with v2.0 (192 KB) James Stott, 2014-01-31 06:13 AM

issue_v2_2.jpg - PgAdmin view with v2.2 (834 KB) James Stott, 2014-01-31 06:13 AM

History

#1 Updated by Jürgen Fischer almost 11 years ago

  • Subject changed from Issue with standalone python script and PostGIS Layers to Lots of PostGIS connections with a standalone python script

#2 Updated by James Stott almost 11 years ago

An update.

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: 
"CLOSE qgisf0_21"
"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 almost 11 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 almost 11 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?

#5 Updated by Jürgen Fischer over 10 years ago

  • Category set to Data Provider/PostGIS

#6 Updated by Giovanni Manghi over 7 years ago

  • Regression? set to No
  • Easy fix? set to No

#7 Updated by Giovanni Manghi over 6 years ago

  • Status changed from Open to Feedback

Please test with a recent QGIS release (2.18 or 3), if the issue/request is still valid change the affected version accordingly, if is fixed/implemented then close the ticket. Thanks!

#8 Updated by James Stott over 6 years ago

I have no idea if this still is the case. I cant remember which script I was testing it with as I reported this over 4 years ago.

Probably best to close it and someone can report it again if they get the same problem.

#9 Updated by Giovanni Manghi over 6 years ago

  • Resolution set to not reproducable
  • Description updated (diff)
  • Status changed from Feedback to Closed

Also available in: Atom PDF