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.

score 50 · Answer 1 · answered Dec 17 '13 at 12:38

50

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

answered Dec 17 '13 at 12:38

piro

13,378
5
34
38

9

Just a note, the syntax needs the trailing 's' character after the integer value if you want to use seconds as the unit: '-c statement_timeout=10s' for 10 seconds. Otherwise it treats the value as milliseconds I believe. – foslock May 12 '16 at 20:48
@piro : What's the default value for `statement_timeout` ? In my version of postgres (9.3) it's set to 0, which looks to me like postgres will let the query run essentially forever lol. Does `psycopg2` work the same way by default? I know this is an old thread but thanks :D – dmn Aug 01 '16 at 16:47
3

@dmn: yes, by default postgres doesn't set any statement timeout. The default can be changed per database (in the config file), per user (ALTER USER ... SET statement_timeout), per connection (as per response above), per session or query (using SET or SET LOCAL). psycopg just uses whatever the server defines, or the customer is configured with, or the connection string configures, it doesn't add anything of its own. – piro Aug 02 '16 at 17:54
Related question - is it possible to set a timeout on the connection rather on per statement basis? – Karl Anka Mar 18 '21 at 13:07
1

@KarlAnka you might be looking for idle_in_transaction_session_timeout – piro Mar 22 '21 at 13:36

score 13 · Answer 2 · answered Nov 20 '13 at 16:26

13

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.

answered Nov 20 '13 at 16:26

fog

3,266
1
25
31

nice to know, however using this per statement would incur an overhead – Jonathan Livni Nov 20 '13 at 19:28
8

This setting is not per statement, this setting applies to all statements made on the connection (or cursor, not sure) after it. – Eddified Aug 13 '14 at 17:54
4

use SET LOCAL for per-transaction settings or SET SESSION for per-session https://www.postgresql.org/docs/9.3/static/sql-set.html – tsionyx Feb 01 '17 at 13:09

score 1 · Answer 3 · answered Aug 11 '17 at 05:46

Looks like PostgreSQL 9.6 added idle transaction timeouts. See:

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

set transaction\query timeout in psycopg2?

3 Answers3

Linked