PostgreSQL: SET-ing from psql arguments

Question

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?

Answer 1

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.

Answer 2

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.

• Davos: 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.