Hi Retool Team,
I am building an inventory management app that records inventory and transactions. Each transaction transforms parent assets into new child assets. Whenever a new transaction is created, the app records the new transaction as well as the mapping from parent assets to child assets. This requires inserting items into 3 separate tables in a Postgres DB in a specific order, then updating quantities of the parent assets.
It is important to wrap these 4 atomic operations into a single transaction. Previously I did this by allowing JS scripting inside DB queries and implementing the transaction in a single query. This was pretty messy and unsafe but got the job done. Now I am moving this functionality to a workflow and I'd like to separate each atomic operation into its own query, and exit differently depending on which query throws an error. I tried issuing seperate queries manually with BEGIN, ROLLBACK and COMMIT but the ROLLBACK doesn't work. I assume this is because every query is wrapped in its own transaction. Is there any way to disable this behavior and do a multi-query transaction currently? I am running Retool 3.12.4 self hosted.
If multi-query transactions in workflows aren't supported, I think they should be. The modular structure of workflows, and the fact that they run in the backend, would be perfect for this approach.