Is there a System Table that Stores Failed SQL?

[Julie]

Hi,

Does anyone know if there is a system table that tracks failed SQL statements?

For instance:

Code: Select all

dbadmin=> select 'a'::int;
ERROR 3681:  Invalid input syntax for integer: "a"
dbadmin=> select message, transaction_id, statement_id from dc_errors where session_id = current_session() order by time desc;
                message                | transaction_id | statement_id 
---------------------------------------+----------------+--------------
 Invalid input syntax for integer: "a" |              0 |           -1
(1 row)

The SELECT statement resulted in an error that I could retrieve from the DC_ERRORS table. However, where can I find the SQL statement? It's not in the QUERY_REQUESTS or DC_REQUESTS_ISSUED tables

Thanks!

[NorbertKrupa]

dc_errors will store a lot more information than busier data collection objects such as dc_requests_issued. You're not seeing this because dc_requests_issued gets turned over much more quickly (based on the set collection policy).

The query I use to show requests with errors is from my Vertica Kit:

Code: Select all

-- Shows any query requests with errors (truncated request text)
-- http://wp.me/p3Qalh-iV
SELECT /*+label(diag_query_errors)*/
       qr.node_name, 
       qr.user_name, 
       qr.session_id, 
       qr.start_timestamp, 
       qr.request_type, 
       LEFT(REGEXP_REPLACE(qr.request, '[\r\t\f\n]', ' '), 100) AS request, 
       qr.request_duration_ms, 
       qr.error_count, 
       em.error_level, 
       em.error_code, 
       em.message 
FROM   v_monitor.query_requests qr 
       JOIN v_monitor.error_messages em 
         ON em.node_name = qr.node_name 
            AND em.session_id = qr.session_id 
            AND em.request_id = qr.request_id 
            AND em.transaction_id = qr.transaction_id 
ORDER  BY qr.start_timestamp DESC;

[Julie]

Hi norbertk,

Thanks for the response. I'm looking to find the actual SQL statement that failed. Like below, the "select 'a'::int;" statement I run is not in the v_monitor.error_messages table per your query:

Code: Select all

dbadmin=> select 'a'::int;
ERROR 3681:  Invalid input syntax for integer: "a"
dbadmin=> SELECT /*+label(diag_query_errors)*/
dbadmin->        qr.node_name,
dbadmin->        qr.user_name,
dbadmin->        qr.session_id,
dbadmin->        qr.start_timestamp,
dbadmin->        qr.request_type,
dbadmin->        LEFT(REGEXP_REPLACE(qr.request, '[\r\t\f\n]', ' '), 100) AS request,
dbadmin->        qr.request_duration_ms,
dbadmin->        qr.error_count,
dbadmin->        em.error_level,
dbadmin->        em.error_code,
dbadmin->        em.message
dbadmin-> FROM   v_monitor.query_requests qr
dbadmin->        JOIN v_monitor.error_messages em
dbadmin->          ON em.node_name = qr.node_name
dbadmin->             AND em.session_id = qr.session_id
dbadmin->             AND em.request_id = qr.request_id
dbadmin->             AND em.transaction_id = qr.transaction_id
dbadmin-> WHERE qr.session_id = current_session() 
dbadmin-> ORDER  BY qr.start_timestamp DESC;
 node_name | user_name | session_id | start_timestamp | request_type | request | request_duration_ms | error_count | error_level | error_code | message 
-----------+-----------+------------+-----------------+--------------+---------+---------------------+-------------+-------------+------------+---------
(0 rows)

No rows were returned (note that I added the WHERE qr.session_id = current_session() clause)...

But the error that occurred is in the dc_errors table:

Code: Select all

dbadmin=> select * from v_monitor.error_messages where session_id = current_session();
       event_timestamp        |      node_name      |      user_id      | user_name |            session_id            | request_id | transaction_id | statement_id | error_level | error_code |                message                | detail | hint 
------------------------------+---------------------+-------------------+-----------+----------------------------------+------------+----------------+--------------+-------------+------------+---------------------------------------+--------+------
 2014-08-26 11:46:03.85852-04 | v_snowfall_node0001 | 45035996273704962 | dbadmin   | vertica01.avnet.com-2306:0x6582c |          0 |              0 |           -1 | ERROR       |   33710210 | Invalid input syntax for integer: "a" |        | 
(1 row)

[NorbertKrupa]

I understand. The query I proposed does that, provided that you have the corresponding query data for the error.

For example, run:

Code: Select all

SELECT MIN(time) FROM v_internal.dc_requests_issued;

SELECT MIN(time) FROM v_internal.dc_errors;

Without changing the data collection policies, the best you can do is just query:

Code: Select all

SELECT * FROM v_monitor.query_requests WHERE error_count > 0;

[Julie]

The query I proposed does that, provided that you have the corresponding query data for the error.

Sorry, but I do not understand what you mean by this? I don't have the original query. It's what I am looking to find If I go directly to the DC_ERRORS table and see that I am getting a bunch of SQL errors, I may want to try and figure out what SQL is causing those errors. But apparently I can't via the meta-data in the system tables.

So I guess the answer to the original question is no, there is not a system table that tracks failed SQL statement

[NorbertKrupa]

I'm hoping to clarify my answer to your question. If I understand correctly, you want to see the statements behind errors from dc_errors. You will only be able to see the corresponding statement IF it hasn't already been turned over in dc_requests_issued (following the data collection retention policies). If you look at the underlying definition for query_requests (it's a view), you'll see that it's actually joining on dc_errors:

Code: Select all

create view "v_monitor"."query_requests" as select ri.node_name,
       ri.user_name,
       ri.session_id,
       ri.request_id,
       ri.transaction_id,
       ri.statement_id,
       ri.request_type,
       replace(replace(ri.request, E'\n', ' '), E'\t', ' ') as request,
       ri.label as request_label,
       ri.search_path,
       round(ra.memory_mb, 2) as memory_acquired_mb,
       rc.success,
       de.error_count,
       ri.time                                   as start_timestamp,
       rc.time                                   as end_timestamp,
       datediff('millisecond', ri.time, rc.time) as request_duration_ms,
       rc.time IS NULL                           as is_executing
from
     v_internal.dc_requests_issued ri
     LEFT OUTER JOIN v_internal.dc_requests_completed rc USING (node_name, session_id, request_id)
     LEFT OUTER JOIN (select node_name,
                       session_id,
                       request_id,
                       count(*) as error_count
                from v_internal.dc_errors
                where error_level >= 20
                group by 1,2,3) de USING (node_name, session_id, request_id)
     LEFT OUTER JOIN (select node_name,
                       transaction_id,
                       statement_id,
                       max(memory_kb)/1024::float as memory_mb
                from v_internal.dc_resource_acquisitions
                where result = 'Granted'
                group by 1,2,3) ra
                USING (node_name, transaction_id, statement_id)
order by is_executing desc, rc.time desc;