SESSIONS table retention

dimitri_p

Hi!

I am trying to localize the source of certain queries seen in QUERY_REQUESTS of my vertica instance.

I get a session_id. The SESSIONS table has the fields I am after:

client_type
client_version
client_os
client_os_user_name
client_authentication_name
client_authentication
requested_protocol
effective_protocol

The problem is, once the query's session_id appears in SESSIONS in 30 seconds it is no longer there. Gone.

Is there a way to get the client details better, than scanning SESSIONS table every second?

PS: I could use vertica.log but it doesn't have all the client fields I need

Sergey_Cherepan_1

You can see past sessions info in system view user_sessions. It will have all fields you are after. And data persist beyond session end.
All queries are saved in dc_requests_issued, including query text in REQUEST field. It does have session_id, you can link to session in user_session.

dimitri_p

It looks like when the query I am after starts - the session_id from QUERY_REQUESTS doesn't appear in SESSIONS right away. It appears there once the query finishes. And if it is the only query in the session, the session information lives in SESSIONS for a fraction of second between the query end time and the session end time, which is near impossible to catch. What do I do?

dimitri_p

Thanks, Sergey!