Function to close sessions by user

Forum|Forum|11 years ago
December 19, 2014
5 replies
21 views

+2

norbert_krupa
Participating Frequently

The function CLOSE_SESSION(session_id) closes a session based off a session. I'd like to request a function that can close all sessions based on a user name. For example, CLOSE_USER_SESSIONS(user_name) which would close sessions based on the user_name.

This is helpful when there's 500+ connections active from a user. Currently, I stage the close session statement from user_sessions.

David_Rydzewski
Participating Frequently
Forum|Forum|11 years ago
December 19, 2014

Bit of a hack, but you could create a Java UDF transform function that takes a set of session ids as input, then inside the UDF connect back to Vertica with JDBC and call close_session on each session_id.

For instance, select my_close_session(session_id) over() from sessions where user_name = 'biff';

Like

David_Rydzewski
Participating Frequently
Forum|Forum|11 years ago
December 19, 2014

Bit of a hack, but you could create a Java UDF transform function that takes a set of session ids as input, then inside the UDF connect back to Vertica with JDBC and call close_session on each session_id.

For instance, select my_close_session(session_id) over() from sessions where user_name = 'biff';

Like

A

+2

Anonymous
Participating Frequently
Forum|Forum|11 years ago
December 19, 2014

or you can do something as simple as

vsql -At -c "select 'select close_session(''' || session_id || ''');' from sessions where user_name = '<user_name>';" | vsql

that will create the statement and pipe it to a new vsql session to be executed.

Hope that makes sense.

Eugenia