How to see currently executed SQL in PL/VSQL stored procedure

Sergey_Cherepan_1Sergey_Cherepan_1 ✭✭✭
edited March 2023 in General Discussion


I am running PL/vSQL stored procedure.

In sessions system table, I can see only call to stored procedure.

How I can see currently executing SQL (if any) for session that is running stored procedure?

Thank you


  • Options
    SruthiASruthiA Vertica Employee Administrator

    @Sergey_Cherepan_1 ; It is getting logged in dc_requests_issued but as a part of different txn id

    I have run below to call the stored procedure. as you can see it logged the query, but as a part of other txn id.

    eonv1203=> call result1();



    (1 row)

    select node_name, transaction_id, statement_id,request from dc_requests_issued order by time desc;

    v_eonv1203_node0001 | 45035996273735120 | 1 | select ($1)::int
    v_eonv1203_node0001 | 45035996273735118 | 1 | SELECT * FROM t1 ORDER BY b DESC
    v_eonv1203_node0001 | 45035996273735117 | 1 | call result1();
    v_eonv1203_node0001 | 45035996273735116 | 1 | CREATE PROCEDURE result1() LANGUAGE PLvSQL AS $$
    x int;
    x <- SELECT * FROM t1 ORDER BY b DESC; -- x is now assigned the first row returned by the SELECT query

  • Options

    Thanks for looking!
    Yes I found that SQL is logged in dc requests issued under different transaction Id.
    Question here - how I can monitor SQL executed in stored procedure.
    I want to see what SQL is being executed right now, and how long it is running.

  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    Stored procedures run in an internal session that you can view with "select * from vs_sessions;"

  • Options

    Interesting... thanks for hint, will investigate how it works.

This discussion has been closed.