Transactional Workflow for postgres (or other transactional databases)

Hi !

In workflow with postgres nodes (should be the same problem with other databases nodes), we update / insert / delete datas during the execution, with data manipulations, preparations or any other things.

If, for any reason, the worflow execution fails on one node (lets say a node near the end of the workflow), all datas inserted / updated / delete in database before this failed node is still inserted / updated / deleted.
But the worflow is not finished. We can not re-execute directly the same workflow, because all the previous datas are already computed, and we can not easily finish the worflow re-starting on the failed execution point.

One solution is to complexify (a lot !) the wokflows and try to handle all the cases if the data is already present, etc… Event in this case it’s not always possible.

Another solution could be to make the full worfklow execution one big transaction.
Ex: a SQL node ‘BEGIN’ (start transaction) at the beginnning of the worfklow and a node ‘COMMIT’ at the end. If the worfklow fails, it automatically rolls back.

Welcome to the community @Thomas!

Thanks a lot for the feature request and already providing possible solutions for the problem. That is very helpful.

I just wanted to follow up very fast because of the part “and we can not easily finish the workflow re-starting on the failed execution point.”. That is actually possible. If a workflow execution fails, it can get started again anytime from the point it did fail (or more from the node before the failure). There is the option to retry again with the version of the workflow with which the execution got originally started with, or it is possible to make changes to the workflow (so for example add more nodes or change existing nodes, just do not rename the node before the failure, that will break it) and then use that new version.
Screenshot from 2021-10-26 07-07-25

Hope that is helpful in the meantime.

Hi, thanks for you’re reply.

:astonished: This explains a lot !! I was sometimes perplex after worfklow retries because the datas where not changed like I wanted (typically by changing a previously executed node and then retry), I understand why now :slight_smile: I was quite sure the retry was retrying all the execution from the start :slight_smile:

I think the transaction feature remains interesting thus, but thanks again for the precision !


Sure, yes totally agree that the transaction feature would still be great anyway, just wanted to unblock you for some of the problems you are seeing.

Glad to hear that it is was helpful! Have fun!

1 Like