在 psycopg2 中设置事务查询超时? [英] set transactionquery timeout in psycopg2?

问题描述

有没有办法在 psycopg2 中为数据库事务或数据库查询设置超时?

示例用例:
Heroku 将 django Web 请求限制为 30 秒，之后 Heroku 终止请求，不允许 django 优雅地回滚任何尚未返回的事务.这可以在 postgres 上留下未完成的交易.您可以在数据库中配置超时，但这也会限制非 Web 相关的查询，例如维护脚本分析等.在这种情况下，通过中间件设置超时 (或通过 django) 会更可取.

解决方案

您可以使用 options 参数设置连接时的超时时间.语法有点奇怪:

<预><代码>>>>导入 psycopg2>>>cnn = psycopg2.connect("dbname=test options='-c statement_timeout=1000'")>>>cur = cnn.cursor()>>>cur.execute("选择 pg_sleep(2000)")回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中psycopg2.extensions.QueryCanceledError:由于语句超时而取消语句

也可以使用环境变量设置:

<预><代码>>>>导入操作系统>>>os.environ['PGOPTIONS'] = '-c statement_timeout=1000'>>>导入 psycopg2>>>cnn = psycopg2.connect("dbname=test")>>>cur = cnn.cursor()>>>cur.execute("选择 pg_sleep(2000)")回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中psycopg2.extensions.QueryCanceledError:由于语句超时而取消语句

Is there a way to set a timeout in psycopg2 for db transactions or for db queries?

A sample use-case:
Heroku limits django web requests to 30sec, after which Heroku terminates the request without allowing django to gracefully roll-back any transactions which have not yet returned. This can leave outstanding transactions open on postgres. You could configure a timeout in the database, but that would also limit non-web-related queries such as maintenance scripts analytics etc. In this case setting a timeout via the middleware (or via django) would be preferable.

解决方案

You can set the timeout at connection time using the options parameter. The syntax is a bit weird:

>>> import psycopg2
>>> cnn = psycopg2.connect("dbname=test options='-c statement_timeout=1000'")
>>> cur = cnn.cursor()
>>> cur.execute("select pg_sleep(2000)")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.extensions.QueryCanceledError: canceling statement due to statement timeout

it can also be set using an env variable:

>>> import os
>>> os.environ['PGOPTIONS'] = '-c statement_timeout=1000'
>>> import psycopg2
>>> cnn = psycopg2.connect("dbname=test")
>>> cur = cnn.cursor()
>>> cur.execute("select pg_sleep(2000)")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.extensions.QueryCanceledError: canceling statement due to statement timeout

这篇关于在 psycopg2 中设置事务查询超时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！