
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!