Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution:

SET statement_timeout TO '1min';

This is great, but sometimes I want to relax the restriction for a specific script.

  • Is there a way to set the timeout as a psql argument? I've tried psql --set statement_timeout=50, unsuccessfully.
  • Is there a way to set it from a Python script which uses psycopg2?

You can use the environment variable PGOPTIONS. Either set it permanently or just when you call psql, e.g.,

PGOPTIONS='--statement-timeout=1min' psql ...

This works for any libpq client, including psycopg.

Of course you could also just put the SET statement in the psqlscript.

| improve this answer | |

As statement_timeout is described in postgres's documentation on client configuration, I would assume it's a setting for the session, so you should be able to just send the set command before your query.

As for how to do it with psycopg2, I have no idea. Looking through the docs shows a set_isolation_level and set_client_encoding ... you might want to look at the code to see how it handles those, and if it's something that's generic and could be extended.

update : I should also mention I didn't see anything in the docs for something like Perl DBI's do, which is how I handle these sorts of things.

| improve this answer | |
  • 1
    I realise how old this is, but adding this for completeness. I also read the docs and couldn't find it as a parameter, but it is done using options parameter and passing -c statement_timeout=1min . That options parameter is a catch-all for passing through any postgres or psql shell args. – Davos Apr 6 '18 at 6:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.