I do most of my geospatial analyses in PostGIS 2.0 using PgAdmin III. I use QGIS 1.8 for visualizing my data but also for querying my database using the DB Manager plugin.

Sometimes (quite often actually...) when I import tables from PostGIS into QGIS using DB Manager and then I run queries on these layers in pgAdmin, they keep on running forever. The only way to bring the queries to stop is to shut QGIS down. Not sure if this is a bug in the plugin or if my PostGIS settings are wrong but I must say I'm well annoyed by this behaviour.

Many thanks in advance.

  • Why don't you make test on a small part of the database ? you would easily know if something is wrong. However, if someone knows how to get informations on time remaining when you do a query from PgAdmin, I would be interested. – Bobo Nov 13 '12 at 10:29
  • @Bobo: I've tried it with small tables containing between 50 and 200 features and it doesn't help. I appreciate your help. – Guillaume Nov 13 '12 at 14:34
  • It's not just you or your PG install; I have to close out of QGIS completely before I'm able to drop/recreate any tables of any size. This was not the case prior to 2.0.1 and happens even if I remove the table from the TOC. – Dylan Hettinger Nov 2 '13 at 16:36

Based on what you've said, it seems like QGIS might not be ending the transaction, but it could be something else entirely.

The easiest way to answer your question is to look at what postgres is doing. I'm assuming your postgres server is on Unix or Mac, because this won't work on windows.

First thing is look at what your postgres processes say they're doing:

% ps axw|grep postgres

You should see some lines that look like this:

19471 ?        Ss     0:00 postgres: someuser somedatabase idle

That shows that username "someuser" is connected to database "somedatabase" from IP address and port 33812. More importantly, it shows that the session is not doing anything – it's idle. That's fine.

However, if you see something like:

19471 ?        Ss     0:00 postgres: someuser somedatabase INSERT

Then you know that the database is doing an INSERT for that session, you might also see "SELECT", "UPDATE", etc.. What you want to do is look for something that isn't quickly going away. If you have a session from your IP address that stays in non-idle mode, especially if it's doing a modification that could be problem.

Another thing you might see is "idle in transaction" – if that persists for a while, then that's very bad and probably indicative of a bug in the client application. That means it has started a transaction, done something, then not ended the transaction, which could block all other access to a table or tables.

If this method doesn't tell you anything, you can start digging deeper. The first place would be to make sure that statement logging is enabled on your postgres server, and then look at the logs it generates and see if anything "funny" is going on. See: http://www.postgresql.org/docs/current/static/runtime-config-logging.html You'll probably want log_statement = all enabled, at least temporarily.

There's more you can do, but this is a good place to start.

Your Answer

