Why some queries don't appear in Vertica's query history

abtsantosbenten

After running an ETL process, the jobs' query didn't appear in the query_profiles or the query_requests catalog. What happened? Proof that the query ran are the vsql (called via shell script files) output log files that contain results of the query. How can we ensure that the queries get logged to the catalog tables for monitoring? Thanks.

Jim_Knicely

What user are you querying the query_profiles or the query_requests as? The ETL user? Admin user?

Note that user's can only see their own queries in those tables;

Example:

dbadmin=> CREATE USER jim;
CREATE USER

dbadmin=> CREATE USER dave;
CREATE USER

dbadmin=> \c - jim
You are now connected as user "jim".

dbadmin=> SELECT current_trans_id(), current_statement(), * FROM dual;
 current_trans_id  | current_statement | dummy
-------------------+-------------------+-------
 45035996273902724 |                 5 | X
(1 row)

Use JIM can see his query:

dbadmin=> SELECT request FROM query_requests WHERE transaction_id = 45035996273902724 AND statement_id = 5;
                           request
--------------------------------------------------------------
 SELECT current_trans_id(), current_statement(), * FROM dual;
(1 row)

But user DAVE cannot see JIM's query:

dbadmin=> \c - dave
You are now connected as user "dave".

dbadmin=> SELECT request FROM query_requests WHERE transaction_id = 45035996273902724 AND statement_id = 5;
 request
---------
(0 rows)