Automatically Close an Idle Vertica Session

[Deleted User][Deleted User] Administrator
edited March 2018 in Tips from the Team

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.


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

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

dbadmin=> SELECT current_session();
(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!

Sign In or Register to comment.