How to set a connection label for vsql... and see it in data collector tables

Sergey_Cherepan_1

Hi,
Apparently vsql is lacking command line option to set connection label.
Meta-function set_connection_label is not affecting data collector tables, according to docs.

Question - how to set connection label from vsql, in a way it will be visible in data collector tables?
Thank you

Bryan_H

It looks like this works as expected to update DC tables, despite the doc note. Let me know if you find any issues:
$ vsql -w Vertica1! -c "select set_client_label('this_is_vsql');select user_name,client_hostname,client_type,client_label from sessions;"
user_name | client_hostname | client_type | client_label
-----------+-----------------+-------------+--------------
bryan | [::1]:32918 | vsql | this_is_vsql
(1 row)

d2=> select * from dc_requests_issued where client_label = 'this_is_vsql';
time | node_name | session_id | user_id | user_name | transaction_id | statement_id | request_id | request_type | label | client_label | search_path | query_start_epoch | request | is_retry | digest
-------------------------------+---------------+-------------------------------+-------------------+-----------+-------------------+--------------+------------+--------------+-------+--------------+-----------------------------------------------------------+-------------------+--------------------------------------------------------------------------+----------+----------------------
2022-09-05 19:55:11.060942-04 | v_d2_node0001 | v_d2_node0001-410726:0x460d2c | 45035996526453358 | bryan | 45035996281654276 | 1 | 3 | QUERY | | this_is_vsql | "$user", public, v_catalog, v_monitor, v_internal, v_func | 19154563 | select user_name,client_hostname,client_type,client_label from sessions; | f | -3511289645001229613

Sergey_Cherepan_1

That would be nice... unfortunately label is changed only in sessions, which runs out of internal memory structures.
user_sessions that runs from data collector tables do not see it.
vsql is missing command line parameter -l or --label, to properly set connection label

Bryan_H

Could you open a support case and direct to me? user_sessions joins from dc_session_starts so we would need to log it there. We'll log a new feature request from your ticket.

mosheg

You can mimic the session label by setting a vsql variable Name to your preferred label string.
For example:

vsql -U user01 -w 12345 -v S_LABEL='My_label_001' -ef - <<-EOF
\set S_LABEL '''':S_LABEL''''
CREATE TABLE my_label_reference AS SELECT SESSION_ID, current_trans_id(), current_statement(), :S_LABEL AS client_label
FROM CURRENT_SESSION;
EOF

vsql -ef - <<-EOF01
SELECT * FROM my_label_reference;
SELECT left(request,80) My_label_001_session_statments FROM QUERY_REQUESTS
WHERE SESSION_ID = (SELECT SESSION_ID FROM my_label_reference where client_label='My_label_001');
EOF01

The above run time output:
CREATE TABLE my_label_reference AS SELECT SESSION_ID, current_trans_id(), current_statement(), 'My_label_001' AS client_label
FROM CURRENT_SESSION;
CREATE TABLE
SELECT * FROM my_label_reference;
           SESSION_ID           | current_trans_id  | current_statement | client_label
--------------------------------+-------------------+-------------------+--------------
 v_eevdb_node0001-1757:0x2b058f | 45035996273891825 |                 1 | My_label_001
(1 row)

SELECT left(request,80) My_label_001_session_statments FROM QUERY_REQUESTS
WHERE SESSION_ID = (SELECT SESSION_ID FROM my_label_reference where client_label='My_label_001');
                          My_label_001_session_statments
----------------------------------------------------------------------------------
 CREATE TABLE my_label_reference AS SELECT SESSION_ID, current_trans_id(), curren
 INSERT INTO public.my_label_reference SELECT CURRENT_SESSION.session_id AS SESSI
(2 rows)

Sergey_Cherepan_1

Thanks for your time putting together an example!
May be I was not very clear what I need.
I have an audit process to save all Vertica sessions. Every hour I am getting new sessions from user_sessions and copying them somewhere.
User session runs from dc_session_start and dc_session_end (sorry if dc table name is misspelled).
Everything in user_sessions has a label set properly, ODBC, JDBC, vertica-python. Even vertica-sql-go client set label on connection that is visible in user_sessions.
Except vsql. Exactly as it said in docs, set_client_label is not reflected in dc_session_start, and not visible in user_sessions.

Yes, set_client_label set it in sessions, which is going away as soon as session ends.

My guess, there are many connection options for vsql. May be, it is time to do connection by connect string - instead of bunch of parameters vsql should just take URL with all parameters inside. ODBC and JDBC do connection string, vertica-python do not.

I can file new feature in support, if needed.