How to stop a PostgreSQL query if it runs longer than the some time limit with psycopg?

Question

In Python is library psycopg and with that I can do queries.

I have an array with texts and I just iterate over them and run query to find this text in postgres. But some time the query takes so much time to execute and in that moment I want to stop/terminate this query and go to the next. For example if query takes 10sec or longer I need to stop it and go to the next.

Is it possible with psycopg? Or maybe it is possible with something else?

Answer 1

Using statment_timeout to cancel statement that runs long. Use options parameter to pass in statement_timeout setting. By default the integer value is in milliseconds. It can be modified with units e.g. 10s = 10 seconds.

import psycopg2

# Set timeout to 1 millisecond for test purposes.
con = psycopg2.connect(options='-c statement_timeout=1', dbname="test", host='localhost', user='postgres', port=5432)

cur = con.cursor()
cur.execute("select * from cell_per")
QueryCanceled: canceling statement due to statement timeout

con.close()
# Set timeout to 10 seconds.
con = psycopg2.connect(options='-c statement_timeout=10s', dbname="test", host='localhost', user='postgres', port=5432)

cur = con.cursor()
cur.execute("select * from cell_per")
cur.rowcount
73

Answer 2

You can use psycopg2 lib and create a connection and create a cursor with timeout

pip install psycopg2

import psycopg2
import threading

connection = psycopg2.connect(dbname="database", user="user", password="password", host="localhost", port=5432)
cursor = connection.cursor()

try:
    threading.Timer(10.0,lambda con: con.cancel() ,args=(connection,)).start() # you can set with threading timeout
    cursor.execute("SELECT * FROM table WHERE column = 'value'")
except psycopg2.extensions.QueryCanceledError:
    pass

cursor.close()
connection.close()

docs : https://www.psycopg.org/docs/