Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Merge Query Adding Duplicate Rows

I am facing a peculiar issue with Merge command. I assume it to be related to WOS\ROS. 

 

I am constantly polling a postgres database, and trying to keep Vertica data in sync. Vertica tables are denormalized form of multiple postgres tables. Merge queries are quite simple. I copy differential data from OLTP into a temp table, and then merge the temp table into target table using the merge key.Merge key is same as the Primary key for the table

 

Initially, I am loading close to 100K rows into a target table. Immediately after the initial load, I am running a unit test which updates the datasource and then tries to run a merge command from source to target table. If the update size is bigger like 30-40K, the merge ends up adding duplicate rows into table. The problem does not happen if the update size is smaller like 3-4K.

 

I also tried running the tuple mover explicitly after the initial load. Still it resulted in same issue. 

 

 Can somebody explain the reason here? Would having a larger WOS would help? Or what other configuration parameters need to be tweaked in order to avert this issue.

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.