The Parts of a Session ID: Quick Tip

High angle view of Beijing Guomao.

Jim Knicely authored this post.

A Vertica Session is an occurrence of a user interacting with a database through the use of SQL statements. Each Session has an associated Session Identifier stored in the SESSION_ID column of many of the Vertica system tables. The Session ID is a unique identifier within the cluster at any point in time, but can be reused after the session closes.

Example:

dbadmin=> SELECT session_id, node_name, client_hostname, client_pid, client_os_user_name
dbadmin-> FROM sessions;
session_id | node_name | client_hostname | client_pid | client_os_user_name
---------------------------------+--------------------+--------------------+------------+---------------------
v_test_db_node0001-16441:0x26b2 | v_test_db_node0001 | ::1:48442 | 54681 | dbadmin
v_test_db_node0002-25835:0xe0a | v_test_db_node0002 | 192.168.1.10:49559 | 323864 | knicely
v_test_db_node0002-25835:0x1070 | v_test_db_node0002 | 192.168.1.10:49994 | 323012 | knicely
(3 rows)

The SESSION_ID is comprised of a Node Name, Process ID and Sequence# having the format NodeName-ProcessID:Sequence#, where the Node Name is the Initiator Node and the Sequence Number is a uniquely (at session create time) Vertica-generated Hex number.

But where does the Process ID come from? It’s the Vertica Process ID on the initiator node!

Example:

dbadmin=> SELECT local_node_name();
local_node_name
-------------------
v_test_db_node0001
(1 row)
dbadmin=> \! ps -ef | grep [5]433| awk '{print $2}'
16441
dbadmin=> \! ssh vertica02 "ps -ef | grep [5]433" | awk '{print $2}'
25835

Helpful link:

https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/SESSIONS.htm

Have fun!