Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

Managing Idle Sessions ​

The database proactively closes both idle sessions and idle transactions within sessions when they reach a timeout limit. You can modify the default settings of the following configuration parameters in order to avoid running out of connections (the maximum number of user connections for the cluster is 2000). These parameters may also help you avoid problems where the presence of idle sessions or idle transactions within sessions holds up background maintenance operations. When these operations are blocked, database storage may reach capacity.

  • idle_session_timeout: how long any user session may remain idle before being disconnected. Any session will time out if it has not run a statement for the specified length of time (regardless of open transactions). The default is 60 minutes (3600000 ms). Connections terminated due to idle_session_timeout will result in a sys.log_session state value of 25P04.
  • idle_in_transaction_session_timeout: how long any transaction within any user session may remain idle before being disconnected. Any session will time out if it has an open transaction that has not run a statement for the specified length of time. The default is 10 minutes (600000 ms). Connections terminated due to idle_in_transaction_session_timeout will result in a sys.log_session state value of 25P03.

Note: Running show idle_session_timeout or show idle_in_transaction_session_timeout as a superuser will always return unlimited (0), which is the superuser's setting, not the default setting for the cluster.

Both parameters accept settings with the following units: ms, s, min, h, and d.

You can modify these parameters with the following commands:

  • ALTER SYSTEM: Only superusers can change these settings with this command, which applies to all sessions for all users. A configuration reload or database restart is required for the new values to take effect. For example:
premdb=# alter system set idle_session_timeout to '2h';
ALTER SYSTEM
premdb=# show idle_session_timeout;
 idle_session_timeout 
----------------------
 1h
(1 row)
  • SET: Any user can change these settings with this command. The new value is applied only to the current user session. The next session for that user resets to the default value. For example:
premdb=# set idle_session_timeout to 999999;
SET
premdb=# show idle_session_timeout;
 idle_session_timeout 
----------------------
 999999ms
(1 row)
  • ALTER ROLE: changes the settings only for the specified role. Restarting the database is not required, and the new settings are applied to all new sessions for that role. You do not have to be a superuser to run this command, but you must have permissions to alter the role in question.
premdb=# alter role bobr set idle_session_timeout to 999999;
ALTER ROLE
premdb=# show idle_session_timeout;
 idle_session_timeout 
----------------------
 1h
(1 row)

Parent topic:Database Administration