Close All Sessions for a User with a Single Command

Jim_Knicely · May 2019

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 session 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 transaction currently running, and closes 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)

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

Have fun!