psycopg2 cursor hangs up when query time is too long

Question

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.

@cardosource in my case – no, because the same query runs fine in pgAdmin. But it hangs when using psycopg2. — the21st, May 24 '23 at 09:15
And if you set a timeout for all queries using "SET SESSION statement_timeout = 500000;", could that help? — cardosource, May 24 '23 at 13:39
Have you seen [Postgres closes connection during query after a few hundred seconds when using Psycopg2](https://stackoverflow.com/questions/56289874/postgres-closes-connection-during-query-after-a-few-hundred-seconds-when-using-p)? — flakes, May 25 '23 at 06:59
@flakes thanks, I haven't. I also found this one just now: https://stackoverflow.com/a/61353359/2761695 — the21st, May 25 '23 at 07:04

score 1 · Answer 1 · answered Sep 01 '15 at 08:33

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'
)

But shouldn't a `QueryCanceledError` or `InternalError` be raised if the statement_timeout is reached, because it terminates the query? — tscizzle, Feb 19 '16 at 20:57

score 0 · Answer 2 · answered May 29 '23 at 17:48

0

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.

answered May 29 '23 at 17:48

Prem Prakash

90
1
10

psycopg2 cursor hangs up when query time is too long

2 Answers2