https://issues.qgis.org/https://issues.qgis.org/favicon.ico2013-04-22T23:54:18ZQGIS Issue TrackingQGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=404432013-04-22T23:54:18ZJürgen Fischerjef@norbit.de
<ul></ul><p>Are you using the browser?</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=406862013-05-02T13:15:31ZNyall Dawson
<ul></ul><p>Yes, I was using the browser - that explains why mref never reached zero.</p>
<p>However, there's something else at play here. I've removed the browser, and now the connections.remove method is called. However, QGIS still seems to hang on to a connection to the view/database.</p>
<p>To explain in more detail:</p>
<p>1. I start a new project and add a PostGIS view "census.vw_birthplace" to my map<br />2. I remove this layer. connections.remove is called by qgspostgreconn.cpp<br />3. A process remains active on the Postgresql database from QGIS - in this case the process is "SELECT pg_get_viewdef(c.oid) FROM pg_class c JOIN pg_namespace nsp ON c.relnamespace = nsp.oid WHERE relname='vw_birthplace' and nspname='census' and relkind='v'" <br />4. This process blocks any further changes to the census.vw_birthplace view<br />5. Closing QGIS removes the blocking process and allows changes to census.vw_birthplace view</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=406892013-05-02T13:22:16ZNyall Dawson
<ul></ul><p>Similarly, if I repeat the process with a table instead of a view the blocking process is:</p>
<p>"SELECT rulename, definition FROM pg_rules WHERE tablename='census_simplified' and schemaname='census'"</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=406912013-05-02T13:55:36ZJürgen Fischerjef@norbit.de
<ul><li><strong>Category</strong> changed from <i>Data Provider/PostGIS</i> to <i>DB Manager</i></li></ul><p>Both queries are apparently is from the db_manager plugin.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=406932013-05-02T14:19:51ZNyall Dawson
<ul></ul><p>Ok, I can confirm that -- if I don't use the DB_manager plugin to add the postgis layers then the connection is correctly released when the layer is removed.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=440472013-08-13T01:21:46ZNyall Dawson
<ul><li><strong>Subject</strong> changed from <i>PostGIS connection is never released</i> to <i>DBManager does not release PostGIS connections</i></li></ul> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=458572013-10-21T23:44:05ZAlexandre Netosenhor.neto@gmail.com
<ul></ul><p>I can confirm this issue in QGIS 2.0 64bit in Windows 7.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=459432013-10-24T12:26:45ZGiuseppe Sucamelibrush.tyler@gmail.com
<ul><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li></ul><p>Fixed in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/5f5cd4cca9130c5dc0891ecdd52ee175cc538aa6" title="dbmanager: be sure to close db cursors (fix #7679)">5f5cd4cca9130c5dc0891ecdd52ee175cc538aa6</a>.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=459842013-10-27T13:47:25ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>Reopened</i></li><li><strong>File</strong> <a href="/attachments/download/6412/locked_view.png">locked_view.png</a> added</li></ul><p>Unfortunately I'm still seeing this after 5f5cd4c. I've attached a screenshot which shows the issue in PG Admin. The steps to reproduce are:</p>
<p>1. Use DB Manager to select a view (in this case "public.vw_test_7679")<br />2. Drag and drop that view to the map. DB manager runs a query "SELECT pg_get_viewdef(c.oid) FROM pg_class ...." (process 7296 in the attached screenshot)<br />3. Close DB Manager, remove the view layer from the map -- the connection for process 7296 is still open<br />4. Attempt to edit the view in PG Admin but it is locked by process 7296.<br />5. Closing QGIS closes the connection and releases the lock</p>
<p>I'm happy to try any diagnose/debugging steps you'd like to help track this down.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=505212014-05-02T01:51:23ZIvan Mincikivan.mincik@gmail.com
<ul></ul><p>Nyall, DB Manager connects with default isolation level which leaves connection in (IDLE in transaction). You can try to change it in PostGisDBConnector class by setting</p>
<p>self.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)</p>
<p>Now you can edit tables in PG Admin when QGIS and even DB Manager are running, but some other things which require for example "named cursors" will not work.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=505222014-05-02T02:00:07ZIvan Mincikivan.mincik@gmail.com
<ul></ul><p>By my quick test only displaying data from table is not working when setting ISOLATION_LEVEL_AUTOCOMMIT for connection.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=505272014-05-02T03:37:42ZIvan Mincikivan.mincik@gmail.com
<ul><li><strong>File</strong> <a href="/attachments/download/7162/connection-holding-fix.patch">connection-holding-fix.patch</a><a href="/attachments/7162/connection-holding-fix.patch"><img alt="Magnifier" src="/images/magnifier.png" /></a> added</li></ul><p>Here is a working patch which will fix connection holding issue by setting DB Manager connection to AUTOCOMMIT isolation level (which is better practice). I have removed named cursor usage in PGTableDataModel._createCursor to fix problems when running with AUTOCOMMIT.</p>
<p>After few minutes of testing all functions it seems OK. Please test.</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=505282014-05-02T03:38:12ZIvan Mincikivan.mincik@gmail.com
<ul><li><strong>Assignee</strong> set to <i>Giuseppe Sucameli</i></li></ul> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=505322014-05-02T08:59:34ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Status</strong> changed from <i>Reopened</i> to <i>Feedback</i></li><li><strong>Pull Request or Patch supplied</strong> changed from <i>No</i> to <i>Yes</i></li></ul><p>Ivan Mincik wrote:</p>
<blockquote>
<p>Here is a working patch which will fix connection holding issue by setting DB Manager connection to AUTOCOMMIT isolation level (which is better practice). I have removed named cursor usage in PGTableDataModel._createCursor to fix problems when running with AUTOCOMMIT.</p>
<p>After few minutes of testing all functions it seems OK. Please test.</p>
</blockquote>
<p>Hi!</p>
<p>it would be better to make a Pull Request on github with your patch, otherwise the risk is that it will not be reviewed.</p>
<p>Thanks!</p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=505472014-05-03T00:53:36ZPaolo Cavallinicavallini@faunalia.it
<ul><li><strong>Assignee</strong> deleted (<del><i>Giuseppe Sucameli</i></del>)</li></ul> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=507702014-05-13T03:37:55ZIvan Mincikivan.mincik@gmail.com
<ul></ul><p>Thanks Paolo, done in <a class="external" href="https://github.com/qgis/QGIS/pull/1348">https://github.com/qgis/QGIS/pull/1348</a></p> QGIS Application - Bug report #7679: DBManager does not release PostGIS connectionshttps://issues.qgis.org/issues/7679?journal_id=547162014-07-25T13:55:19ZGiuseppe Sucamelibrush.tyler@gmail.com
<ul><li><strong>Status</strong> changed from <i>Feedback</i> to <i>Closed</i></li><li><strong>Resolution</strong> set to <i>fixed/implemented</i></li></ul><p>Your pull request was merged in <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/d522059524770ee80402400494f44436317c15b1" title="Merge pull request #1348 from imincik/db_manager_connection_holding db manager: fix connection h...">d522059524</a>. Thanks Ivan.</p>