Postgres: Timeouts

 To determine or check timeout settings in PostgreSQL, you can use the SHOW command for individual parameters or query the pg_settings system view for a comprehensive list. 

1. View Current Timeout Settings 

You can check the current value for any specific timeout parameter in your active session using the following SQL commands: 

Query Timeout: SHOW statement_timeout; (Stops queries that run too long)

Lock Timeout: SHOW lock_timeout; (Stops a command if it can't acquire a lock quickly enough)

Idle Transaction Timeout: SHOW idle_in_transaction_session_timeout; (Closes sessions left open but inactive within a transaction)

Idle Session Timeout: SHOW idle_session_timeout; (Closes sessions that are completely inactive)

Transaction Timeout (v17+): SHOW transaction_timeout; (Sets a limit for the entire duration of a transaction) 

2. Search All Timeout Parameters

To find every setting that contains "timeout" in its name and see its current value, use the pg_settings view: 

sql

SELECT name, setting, unit, short_desc 

FROM pg_settings 

WHERE name LIKE '%timeout%';

3. How Timeouts are Configured

PostgreSQL allows you to define these settings at various levels of precedence: 

Session Level: Change it only for your current connection.

SET statement_timeout = '30s';

User/Role Level: Apply it automatically whenever a specific user logs in.

ALTER ROLE my_user SET statement_timeout = '60s';

Database Level: Set a default for everyone connecting to a specific database.

ALTER DATABASE my_db SET statement_timeout = '2min';

Global Level: Set in the postgresql.conf file, affecting the entire server instance. 

Summary of Key Timeouts

Parameter        Default                  Purpose

statement_timeout 0 (off) Aborts any statement exceeding the specified time.

lock_timeout 0 (off) Aborts if a lock (e.g., for an ALTER TABLE) isn't granted in time.

idle_in_transaction_session_timeout 0 (off) Terminate sessions idle while holding a transaction open.

transaction_timeout 0 (off) Caps the total age of a transaction (available in PostgreSQL 17+).