terminating connection due to idle-in-transaction timeout
0
I'm using postgreSQL as database in production environment. There is an excel upload button which calls a microflow, and I set it as Asynchronous type. The excel parsing function in microflow was a custom javaaction and it worked fine. It returns a list and the amount is around 10 thousand. I use a loop to map the result(retrieve some attribute and create object which needs to commit). After the loop I delete the old data in database(retrieve all and delete), then commit the new list. At that commit point, error occurred, "FATAL: terminating connection due to idle-in-transaction timeout". The time cost before commit activity is around 4min. The idle-in-transaction-session-timeout is set 60s in database setting, but I don't think commit activity takes that long. So I want to know how this happened, how to solve it, and when the connection between the program and the database was established, and why it went to "idle-in-transaction" state? best regaurd!
asked
wei gang
1 answers
0
This error happens when you have an open transaction that is blocking other users. My suspicion would be the custum Java action. You say it returns a list, but are those objects committed and is the transaction closed correctly? Because if not and you are working on the list also and changing stuff I could see the reason for this error.
What I do with large Excel imports is always import first to temp objects and first make sure everything is in the database. Then start the action to process all the data. And afterwards remove all the records that are processed correctly and keep the records where their are problems so you can manually check those records on why they are failing.