Automatically Close an Idle Vertica Session

[Deleted User]

This tip was authored by Jim Knicely.

The IDLESESSIONTIMEOUT parameter can be used to close a session that has been idle for a period of time. An idle session is one that has no queries running.

Example:

dbadmin=> CREATE USER jim IDLESESSIONTIMEOUT '10 seconds';
CREATE USER

dbadmin=> \c - jim
You are now connected as user "jim".

dbadmin=> SELECT current_session();
        current_session
-------------------------------
v_test2_node0001-3667:0x2a19e
(1 row)

dbadmin=> -- Wait 10 Seconds

dbadmin=> SELECT COUNT(*) FROM sessions;
FATAL 7540:  Session idle for more than 10000 ms. Session Timed Out!
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

The DC_ERRORS data collector table tracks sessions that were closed because of an idle timeout:

dbadmin=> SELECT user_name, message, error_level_name FROM dc_errors WHERE session_id = 'v_test2_node0001-3667:0x2a19e';
 user_name |                         message                         | error_level_name
-----------+---------------------------------------------------------+------------------
 jim       | Session idle for more than 10000 ms. Session Timed Out! | FATAL
(1 row)  

Have Fun!