Duplicate Query is exists in the query_requests system table

HyeontaeJu

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

Hibiki

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.

LenoyJ

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.

Hibiki

@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.

HyeontaeJu

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

Hibiki

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?

HyeontaeJu

@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.

Hibiki

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