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.