Close All Sessions for a User with a Single Command

Posted May 9, 2019 by James Knicely, Vertica Field Chief Technologist

Tips and Tricks Orange Button
The Vertica CLOSE_SESSION function can be used to close a user session one session at a time. If you have a user that has opened a bunch of sessions, closing them one at a time would be a very long and tedious process.

That’s where the CLOSE_USER_SESSIONS function comes in handy. It will stop all the sessions for a user, rolling back any transactions currently running, and close the connection.

Example: dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = 'jim'; COUNT ------- 33 (1 row) dbadmin=> SELECT session_id FROM sessions WHERE user_name = 'jim' LIMIT 2; session_id ----------------------------------- v_test_db_node0001-22211:0x20f7e7 v_test_db_node0001-22211:0x20f7ef (2 rows) I could close the sessions one by one: dbadmin=> SELECT CLOSE_SESSION('v_test_db_node0001-22211:0x20f7e7'); CLOSE_SESSION -------------------------------------------------------------------- Session close command sent. Check v_monitor.sessions for progress. (1 row) dbadmin=> SELECT CLOSE_SESSION('v_test_db_node0001-22211:0x20f7ef'); CLOSE_SESSION -------------------------------------------------------------------- Session close command sent. Check v_monitor.sessions for progress. (1 row) dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = 'jim'; COUNT ------- 31 (1 row But that would take forever. Instead, I’ll close all of the sessions at once! dbadmin=> SELECT close_user_sessions('jim'); close_user_sessions ------------------------------------------------------------------------------ Close all sessions for user jim sent. Check v_monitor.sessions for progress. (1 row) dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = 'jim'; COUNT ------- 0 (1 row) Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_USER_SESSIONS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_SESSION.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/SESSIONS.htm

Have fun!