set transaction\query timeout in psycopg2?

Question

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.

Answer 1

Looks like PostgreSQL 9.6 added idle transaction timeouts. See:

• https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT for reference.
• http://blog.dbi-services.com/a-look-at-postgresql-9-6-killing-idle-transactions-automatically/ as an example.

PostgreSQL 9.6 is also supported in Heroku so you should be able to use this.

Answer 2

You can set a per-statement timeout at any time using SQL. For example:

SET statement_timeout = '2s'

will abort any statement (following it) that takes more than 2 seconds (you can use any valid unit as 's' or 'ms'). Note that when a statement timeouts, psycopg raises an exception and it is your care to catch it and act appropriately.

Answer 3

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