I am sometimes getting an error while using the Delete matching database rows action, which causes my flow to stop, and sometimes the rows have been deleted while other times the rows are still in the database.
The database I am using is an AWS’ RDS PostgreSQL
The strange part is that I can´t find what is causing the error, sometimes it works well and it sometimes fail.
When it fails I get this 3 errors in a row:
1.Rollback failed. The database table may contain incompletely deleted data.
2.This NpgsqlTransaction has completed; it is no longer usable.
3.Exception while reading from stream Details: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.; A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
I tried a work around, using another action, “Delete database table rows” with a query, and iterating that action inside a module. This works better since it doesn´t stop when it fails, and I can only rewrite the information that was succesfully deleted. But sometimes this actions gets the same errors in some iterations.
Has anyone had a similar problem? Any ideas how it could be fixed?
It seems that you are using the “Delete rows where key match” mode. Am I right?
Can you please answer a couple of questions:
- Which connector are you using - native or ODBC?
- How many columns and rows are matched by the action?
- How much time does it takes for the action to fail?
Yes, that is the action I am using.
I can answer the first two questions, the third one I am not so sure.
- I am using the postgres connector from easymorph.
- the action is deleting aproximately 300 000 rows, from a table with 80 columns. The match is donde with 3 columns.
- Since the project has other actions that can take a variable time, I am not sure how long it takes for the action to fail. But I can estimate it takes around 10 min for the action to fail.
What would be the best reliable way to delete this amount of records from a postgres table?
Thanks for your answer.
Sebastian, thank you for the answers.
This looks like a timeout issue to me.
Can you please try to increase the SQL command timeout option in the settings of the corresponding connector (at the Advanced tab)? The default value is 30 seconds, can you please set it to 60?
Increasing the timeout worked. I already had it in 60 seconds, but I increased it further to 120 seconds. Is there a drawback in increasing this number much?
The only drawback that I can think of is that in some cases when a connection to the database server is not possible, an EasyMorhp action will hang for 120 seconds before issuing a connection error.