Psycopg request hangs even if cursor is closed

Question

We use an object that keeps connection to PostgreSQL database and creates new cursors to serve requests. I observed strange behavior: even when the response was read and the cursor is closed, the request is still hanging in the database, preventing updating the table etc etc.

When the connection is closed, it disappears.

I know about ORM frameworks and maybe will end up using one of them, but I just want to understand what's happening here. Why the request is still there?

Here's the python code:

import psycopg2

def main():

    conn = psycopg2.connect("dbname=tmpdb password=1 host=localhost")

    cur = conn.cursor()
    cur.execute("SELECT 1;")
    items = cur.fetchall()
    cur.close()
    #uncommenting the following line solves the problem
    #conn.close()

    print items
    while True:
        pass
main()

Here's how to start the code:

>python test_loop.py
[(1,)]

Here's how to observe hanging request:

tmpdb=# SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity ;
 datname | usename  |  pid  | client_addr | waiting |          query_start          |                                          query                                           
---------+----------+-------+-------------+---------+-------------------------------+------------------------------------------------------------------------------------------
 tmpdb     | savenkov |   530 | ::1         | f       | 2013-08-12 13:56:32.652996+00 |             SELECT 1;
 tmpdb     | savenkov | 88351 |             | f       | 2013-08-12 13:56:35.331442+00 | SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity ;
(2 rows)

So actually for now I use `SELECT 1;` after any meaningful requests as a workaround, but really look forward proper solution of this issue. — savenkov, Aug 12 '13 at 15:06

score 1 · Answer 1 · edited Oct 04 '21 at 09:13

1

Why do you think it is blocking?

Create the table

create table t (i integer);

Now run it:

import psycopg2

def main():

    conn = psycopg2.connect("dbname=cpn")
    cur = conn.cursor()

    cur.execute("SELECT i from t;")
    items = cur.fetchall()
    print items

    raw_input('Enter to insert')

    cur.execute("insert into t (i) values (1) returning i;")
    items = cur.fetchall()
    conn.commit()
    cur.execute("SELECT i from t;")
    items = cur.fetchall()
    print items

    raw_input('Enter to update')

    cur.execute("update t set i = 2 returning i")
    items = cur.fetchall()
    conn.commit()
    cur.execute("SELECT i from t;")
    items = cur.fetchall()
    print items

    cur.close()

    while True:
        pass
main()

Notice that you need to connection.commit() for it to be commited.

With that said don't do connection management. In instead use a connection pooler like Pgbouncer. It will save you from lots of complexity and frustration.

If the application runs on the same machine as the db then don't even bother. Just always close the connection as frequently as necessary. If both are in a fast intranet it is also not worth the added complexity of a connection pooler unless there is a really huge number of queries.

edited Oct 04 '21 at 09:13

Pro Q

4,391
4
43
92

answered Aug 12 '13 at 16:18

Clodoaldo Neto

118,695
26
233
260

@clodaldo-neto, you can check what's left in the requests table using the command I posted above (`SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity;`). I run your script, the last select is hanging in the requests table. Try run your script and after it fetched everything and is looping in the infinite cycle, make 'drop table t;' - it won't work. As for creating and dropping connection each time - that works, but not for, say, API serving lots of requests. – savenkov Aug 13 '13 at 05:24
@savenkov It is not hanging. You left the connection open and there it is in an idle state. Do a `select * from pg_stat_activity` and check the meaning of each column: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW. For a `drop` to work you must issue a `connection.comit()`. – Clodoaldo Neto Aug 13 '13 at 11:40
@clodaldo-neto, thanks, yep, it's idle, I was wrong about 'hanging'. Cool, I will try to do commits after each select, let's see if it solves the problem. Are there any docs on how commit is related to closing idle requests? – savenkov Aug 15 '13 at 11:21
@savenkov You still didn't get it. A select does not need a commit, just the commands that modify the database such as update, insert, delete and ddl commands. You don't close idle request as there are no idle requests. There are idle connections which are idle because you didn't close them. If all that is strange to you then don't, and I really mean it, don't manage connections as it will hurt you. Always close them and just use pgpool **if** at all necessary. – Clodoaldo Neto Aug 15 '13 at 11:30
@clodaldo-neto, as far as I see, the last request in the idle connection effectively blocks the table from dropping. And after some other request is executed, the table can be dropped again. I don't see how does your comment explain this behavior. – savenkov Aug 16 '13 at 10:50
"A select does not need a commit, just the commands that modify the database". I'm still learning, but I did some research and found that a commit *does* need to be done even for SELECTs to avoid some problems. In my case a program was hanging until I did so. No room to list all, but see "idle in transaction" http://initd.org/psycopg/docs/faq.html , http://stackoverflow.com/questions/12608288/use-try-except-with-psycopg2-or-with-closing , or http://stackoverflow.com/questions/2979369/databaseerror-current-transaction-is-aborted-commands-ignored-until-end-of-tra for example. Hope that helps. – Matthew Cornell Feb 04 '14 at 12:51

Psycopg request hangs even if cursor is closed

1 Answers1