如何设置语句超时以执行查询 [英] How to set statement timeout for query execution
问题描述
在我的Web应用程序中，一些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/interfaces .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屋！
