Qlik Replicate Task is not working with Insert Update Conflict Error

[Prabha-Ande]

We are trying to replicate tables from Source (Postgres SQL) to Target (Snowflake) using Qlik Replicate. Full load gets completed successfully and suddenly we start seeing conflict error of insert update in CDC.
Here Replication Task tries to update the record before inserting it and this conflict being written to attrep_exception_table. All the pending transactions are stored in disk/memory as a result there is very high latency for CDC. This increases PostgreSQL slot size  drastically. We have to stop the task and drop the slot to bring the vitals back to normal w.r.t. Postgres Database.

P.S. We have soft delete column defined as part of Global Transformation

Logs from the task are as below

00617923: 2024-01-24T06:39:08:155453 [ASSERTION       ]W:  The 'INSERT' event received for table 20 after 'UPDATE (3)' event with the same primary key at stream position '00009516/BC076AD8.115.00009516/BC0FC048'  (bulk_apply.c:1710)

When we check the CT table transaction for the timestamp - we see insert and update as below 

Can someone help us understand the issue and suggest resolution?

#Qlikreplicate#Dataintegration#Upsertissue#applyconflict#attrepexception#softdelete

[deepaksahirwar]

Dear @Prabha-Ande ,

Thank you for reaching out to us on Qlik Community.

It seems like you’re encountering an “Insert Update Conflict Error” during the Change Data Capture (CDC) process. This error typically occurs when the Replication Task tries to update a record before inserting it

The root cause of this issue could be related to the ‘Store Changes’ option you’re using for capturing changes from the source database. This option treats any change as an INSERT, regardless of the actual operation. Therefore, when you enable the UPSERT mode for resolving apply conflicts, you may encounter errors like ‘No record found for applying an UPDATE’ or ‘The INSERT event received for table 20 after UPDATE event with the same primary key’.

One possible solution is to use the ‘Apply Changes’ option instead of the ‘Store Changes’ option. This way, Qlik Replicate will capture the actual operations (INSERT, UPDATE, DELETE) from the source database and apply them accordingly to the target database. You can also configure the apply conflict settings to suit your business needs, such as ignoring errors, skipping changes, or using UPSERT mode.

Additionally, if you’re using a soft delete column as part of Global Transformation, you might want to ensure that the global expressions are propagated to all tables. If not, you might need to define a soft delete transformation on each individual table as needed.

Lastly, high latency for CDC and increased PostgreSQL slot size could be due to excessive manipulations/transformations or due to the storage of pending transactions in disk/memory. You might want to check the performance and tuning of your Replicate task.

I hope this helps you troubleshoot and resolve the issue.

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

We appreciate your cooperation and feedback. 😊

Best Regards,

Deepak 

[Heinvandenheuvel]

Whenever Replicate uses "[ASSERTION       ]" in the log it really means BUG!

It indicates that Replicate encountered a situation it did not expect to ever happen (but it checked anyway)

The task is using CDC in BATCH OPTIMIZED APPLY right? - that's required for Snowflake.

In this case the expected Replicate bulk apply behavior is to 'see' the Insert and Update and combine it into a single Insert with the updated columns.

The timestamp on the the update seems to be earlier then the insert - good catch. This is very strange and needs to be explained. Maybe someone handy with reporting on the PostgreSQL transaction log can help?

As workaround you could try to enable the exception handling as 'insert record when not found for update' and 'update if already exists for insert'. However, there is some change the insert value may 'win' and the result in the target will be stale.

At any rate, this issue should be reported through a support case for proper attention. "ASSERTIONS" just should not be happening. Please provide as much details as you can with the case.

 Hein

[john_wang]

Totally agree with @Heinvandenheuvel , please open a support ticket , we need deeper investigation on this issue.

I'm not sure it's bug or not at present however this issue related with some special behaviors:

1- Qlik Replicate supports PostgreSQL "REPLICA IDENTITY" set to DEFAULT only

2- The SOFT DELETE is used in the task

3- Snowflake special behavior with soft delete: it first triggers a physical delete on the record and then using using attrep_changes table triggers an insert statement

While all the above behaviors combine into one task, maybe some additional settings need to be considered.

thanks,

John.