Duplicate Query is exists in the query_requests system table

HyeontaeJuHyeontaeJu Vertica Customer

hello, These days, we are using vertica db and make a system.
today, i'm testing the vertica db, and i found something wrong in the query_requests.
Almost 90% of queries have different transaction IDs, which occur twice in the query_requests.

Why Almost 90% of queries occur twice in the query_requests.
Example

Answers

  • HibikiHibiki Vertica Employee Employee

    We are working on it on the support request case. It seems the statements are the same. So I think you or your team have raised it. We will let you know when we have any updates.

  • LenoyJLenoyJ - Select Field - Employee

    Check if the queries are retrying (maybe a join spill). If so, one of the queries will have success=true and the other will not.

  • HibikiHibiki Vertica Employee Employee

    @HyeontaeJu As you may know, MERGE statement can perform INSERT and UPDATE operation with one statement.
    https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Tables/MergeTables/SynchronizingTableDataWithMerge.htm
    In addition, UPDATE operation on Vertica Database is the combination of DELETE and INSERT operation.
    https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/UPDATE.htm
    So when you run the MERGE statement, DELETE and INSERT operation are run internally.
    I looked into your statement, and I confirmed there were 2 entries in query_requests and 1 entry was for DELETE operation, 1 entry was for INSERT operation. You can see each explain plan by searching by transaction ID and statement ID.

  • HyeontaeJuHyeontaeJu Vertica Customer

    @Hibiki but,, the query is select query in the screenshot.

  • HibikiHibiki Vertica Employee Employee

    Oh, I see. I have checked the queries in the uploaded file. For these SELECT statements, can you provide the all records in QUERY_REQUESTS table, QUERY_PROFILES table, QUERY_EVENTS table associated with these transactions? Can you see any error message in vertica.log file when you run these SELECT statements?

  • HyeontaeJuHyeontaeJu Vertica Customer
    edited October 2020

    @Hibiki
    Sorry. I'm busy yesterday, so I'll send you a late reply.
    Oh, sorry, I'm confused. The program calls the query twice.

  • HibikiHibiki Vertica Employee Employee

    Thank you for letting me know it. I understand these 2 SELECT statements were run by your application.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file