CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
savenkov
savenkov

Reputation: 668

Psycopg request hangs even if cursor is closed

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)

Upvotes: 2

Views: 3189

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125264

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.

Upvotes: 1

Related Questions