CODEWITHSUNDEEP
postgresqltimeout
Erik
Erik

Reputation: 7342

psql set default statement_timeout as a user in postgres

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?

Upvotes: 50

Views: 151775

Answers (3)

nijave
nijave

Reputation: 598

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

Upvotes: 4

Suzanne
Suzanne

Reputation: 691

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

Upvotes: 57

khampson
khampson

Reputation: 15306

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;

Upvotes: 84

Related Questions