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 appliance 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).
  • 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).

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 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)