I want to set a default statement_timeout for my access to a postgres database. After configuring my environment variables, I now have it where psql logs me on my preferred database and table. However, while I'm exploring several of tables in it, I'd like to have a statement timeout of around a minute. This can be done simply by typing SET statement_timeout TO '1min'; at the beginning of each session, but this is obnoxious to type every time. I don't have access to the server configuration nor would I want to change it. Ideally I could do something to the effect of alias psql='psql -c "SET statement_timeout TO '1min';"' except the-c` flag of psql doesn't allow interactive input. Are there any nice solutions to this problem, or am I always doomed to set the timeout manually for each interactive session?
3 Answers
You could use your .psqlrc file (if you don't have one in your home directory, create it; if you're on Windows instead of *nix, the file is %APPDATA%\postgresql\psqlrc.conf instead) and set the following command:
set statement_timeout to 60000; commit;
That setting is in milliseconds, so that'll set the timeout to 1 minute. .psqlrc isn't used with -c nor -X invocations of psql, so that should allow you to get your interactive-mode timeout to 1 minute.
You can then execute the following in psql to verify that the configuration has taken effect:
show statement_timeout;
4 Comments
set [...] as a transaction, and so complained about commit with psql:~/.psqlrc:1: WARNING: there is no transaction in progress. Otherwise it worked. Thanks!
Postgres allows you to set configuration parameters such as statement_timeout on a per-role (user) level.
ALTER ROLE <your-username> SET statement_timeout = '60s';
This change will apply to all new sessions for that user, starting on the next login.
Source: Postgres docs
If you're using a client that uses libpq under the hood (most of them except Java and .NET, I think), you can use the PGOPTIONS environment variable.
export PGOPTIONS="-c statement_timeout=7200000"
If you're using the URI-type connection string, you can add options to the options URI parameter in the same format, as long as they're properly uri encoded (percent encoding)
psql "postgres://user:pass@server-hostname/db_name?options=-c%20statement_timeout%3D3600000
When using keyword-value connection strings, you can specify options like this (single quotes required)
psql "host=server-hostname user=user dbname=db_name options='-c statement_timeout=3600000'"
Additionally, the Connection Service File (~/.pg_service.conf) may be useful and follows the same conventions as the above config. See https://www.postgresql.org/docs/current/libpq-pgservice.html
- libpq environment variables https://www.postgresql.org/docs/8.1/libpq-envars.html
- URI parameter information https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
- keyword-value documentation https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-KEYWORD-VALUE
