CODEWITHSUNDEEP
pythonpython-3.xpsycopg2
Nicolai
Nicolai

Reputation: 5797

psycopg2 cursor hangs up when query time is too long

I have a problem with executing long time queries using psycopg2 in Python. When query takes more than 180 seconds the script execution hangs up for a long time.

I use Python 3.4.3 and psycopg2 2.6.1.

Here are samples to reproduce the issue:

import psycopg2

cnn = psycopg2.connect(
    database='**********',
    user='**********',
    password='**********',
    host='**********',
    port=5432,
)
print("Connected")
cursor = cnn.cursor()
seconds = 5
print("Sleep %s seconds"%seconds)
cursor.execute("SELECT pg_sleep(%s);"%seconds)
print("Exit.")

Script works fine when query takes 5 seconds:

$python3 /tmp/test.py 
Connected
Sleep 5 seconds
Exit.

But when number of seconds is about 180 and greater, the line cursor.execute hangs up and instructions below are never executed:

import psycopg2

cnn = psycopg2.connect(
    database='**********',
    user='**********',
    password='**********',
    host='**********',
    port=5432,
)
print("Connected")
cursor = cnn.cursor()
seconds = 180
print("Sleep %s seconds"%seconds)
cursor.execute("SELECT pg_sleep(%s);"%seconds)
print("Exit.")

Here is a output (print("Exit.") never executed as hangs up):

$python3 /tmp/test.py 
Connected
Sleep 180 seconds
<Never exit>

Does anyone know how to solve this problem? Thank you.

Upvotes: 8

Views: 4121

Answers (2)

Prem Prakash
Prem Prakash

Reputation: 120

In simple words, you have to set the timeout option, because you will need the function to exit after some time.

conn = psycopg2.connect(db_url, options='-c statement_timeout=300000')  # timout in ms

This works for me.

Upvotes: 0

Anne M.
Anne M.

Reputation: 171

You might have a statement timeout set somewhere. Try to turn it off for a single statement:

cursor = cnn.cursor()
seconds = 180

# Turn statement_timeout off for the next query
cursor.execute("SET statement_timeout = 0")

print("Sleep %s seconds"%seconds)
cursor.execute("SELECT pg_sleep(%s);"%seconds)
print("Exit.")

If this works, change the default, whereever you have defined it, or just for your connection:

cnn = psycopg2.connect(
    database='**********',
    user='**********',
    password='**********',
    host='**********',
    port=5432,
    options='-c statement_timeout=0'
)

Upvotes: 1

Related Questions