Delete timeouts on "public"."attrep_changesXXXX" postgres server target

[danielrf]

I have random errors on a replicate task , this is the error , but i never faced that before. I checked the postgres server health and it seems all good.

This error happens for diferent source tables, but always on "public"."attrep_changesXXXX" target pg table

Source:  (Oracle

Target: (PostgreSQL)

Error Code: 1022506

Error details: Error forwarding bulk

Failed to apply DELETE (5) for table 18

Failed to execute delete statement.

DELETE  FROM "ORA1111"."SPECIAL_INS" USING "public"."attrep_changes735B7C9CA8F6C822" WHERE  ("ORA1111"."SPECIAL_INS"."CODE"= CAST( "public"."attrep_changes735B7C9CA8F6C822"."seg1" as  VARCHAR(20))  OR ("ORA1111"."SPECIAL_INS"."CODE" IS NULL AND..... long query.....

Error from pg:

RetCode: SQL_ERROR  SqlState: 57014 NativeError: 1 Message: ERROR: canceling statement due to statement timeout; Error while executing the query Failed (retcode -1) to execute statement....

------------------

Coudl it be related that in the oracle source table we have unique index as record identifier,  not specific declared primary keys?

CREATE UNIQUE INDEX "SPECIAL_INS_U_SPECIAL_INS" ON "TARGET_SCHEMA"."SPECIAL_INS" USING btree ("CODE", "CLIENT_ID", "REFERENCE_ID", "LINE_ID", "SEQUENCE", "TYPE");

[Dana_Baldwin · Accepted solution]

Hi @danielrf 

Thank you for the detailed explanation of the issue.

1. It should not matter if the index on the source table is a UI or a PK - but it is relevant if there is a UI or PK on the target table, as this will improve performance of updates or deletes (should not matter if it is a UI or PK, just needs one or the other).

2. If there is a UI or PK on the target table (not the changes table) then there is some type of performance issue on the target database or host it is on when the issue happens (CPU bound, memory consumption, database load, etc). To root cause the issue please work with the target DBA and the server admin.

3. You might be able to work around this issue by increasing the timeout values, but that will not resolve the root cause:

a. On the advanced tab of the target endpoint, scroll down and click Internal Parameters.

b. In the Search Parameter field, type an exclamation point. This will give you a list of parameters (this is easier than typing them in as they are case sensitive).

Select: executeTimeout
and: loadTimeout

c. Set the values to 3x or 4x their default (60 seconds and 1200 seconds respectively). Please note you may need to adjust these values higher if you continue to see this issue.

d. Click OK to save your edits and close the parameter window.

e. Click Save on the endpoint to save your edits.

f. You will need to stop and resume the tasks that use this endpoint before the new settings take effect (endpoint settings are only read and processed during task startup).

Thanks,

Dana

[danielrf · Accepted solution]

Thanks, we reached out db team and it seems the issues happens at the same time on backup scheduling.

So that was the root cause.