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

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?

CodePudding user response：

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/

CodePudding user response：

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