如何为查询执行设置语句超时 [英] How to set statement timeout for query execution
问题描述
在我的网络应用程序中，一些 postgres sql 查询需要很长时间才能执行.我只想为其中的一部分设置语句超时.
In my web app some postgres sql queries take along time for execution. I want set statement timeout for only part of them.
一部分查询必须因超时取消，但其他部分必须不受任何限制地工作.
One part of queries must canceled by timeout, but other must work without any restriction.
在 postgres 中存在 statement_timeout 函数.
In postgres exist statement_timeout function.
如何使用 statement_timeout 函数包装 SqlAlchemy 查询?
How to wrap SqlAlchemy query with statement_timeout function?
像这样:
SET statement_timeout TO 1000; -- timeout for one second
<sqlalchemy generated query>;
RESET statement_timeout; -- reset
对我来说为这样的查询设置超时的完美方式:
Perfect way for me set timeout for query like this:
users = session.query(User).timeout(0.5).all()
SqlAlchemy 必须:1) 设置语句超时 2) 执行查询并返回结果 3) 重置当前会话的语句超时
SqlAlchemy must: 1) set statement timeout 2) execute query and return result 3) reset statement timeout for current session
可能是设置查询执行超时的其他方式?
May be other way to set timeout for query execution?
更新 1. 我的解决方案
我的解决方案是自定义连接代理(使用 psycopg2==2.4 和 SQLAlchemy==0.6.6 测试):
My solution is a custom connection proxy (tested with psycopg2==2.4 and SQLAlchemy==0.6.6):
from sqlalchemy.interfaces import ConnectionProxy
class TimeOutProxy(ConnectionProxy):
def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
timeout = context.execution_options.get('timeout', None)
if timeout:
c = cursor._parent.cursor()
c.execute('SET statement_timeout TO %d;' % int(timeout * 1000))
c.close()
return execute(cursor, statement, parameters, context)
engine = create_engine(URL, proxy=TimeOutProxy(), pool_size=1, max_overflow=0)
这个解决方案没有重置statement_timeout，因为每个SqlAlchemy查询都是在隔离事务中执行的，并且statement_timeout 在当前事务中定义.
This solution without reseting statement_timeout, because each SqlAlchemy query executed in isolated transaction and statement_timeout defined inside current transaction.
用法示例(以秒为单位的超时参数):
Usage example (timeout pаram in seconds):
Session.query(Author).execution_options(timeout=0.001).all()
Session.bind.execute(text('select * from author;')
.execution_options(timeout=0.001))
.fetchall()
推荐答案
您应该查看 SQLAlchemy <= 0.6 提供的扩展:
You should look at the extensions provided with SQLAlchemy <= 0.6:
http://www.sqlalchemy.org/docs/06/orm/接口.html
有一些钩子，您可以在其中插入代码以进行单个操作.
There are hooks where you could stick in your code for individual operations.
SQLAlchemy 0.7+ 现在有一个事件系统……可能有类似的东西.见
SQLAlchemy 0.7+ now has an event system...there might be something similar. See
http://www.sqlalchemy.org/docs/core/events.html
这篇关于如何为查询执行设置语句超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
