'How to deal with FATAL: terminating connection due to idle-in-transaction timeout

I have one method where some DB operations are happened with some API call. This is a scenario with Spring and postgres DB. We also have property set for idle transaction in postgres DB

idle_in_transaction_session_timeout = 10min

Now the issue is I do get Exception sometime

org.postgresql.util.PSQLException: This connection has been closed. Root Cause is FATAL: terminating connection due to idle-in-transaction timeout

For example, my code look like this:

@Transactional(value = "transactionManagerDC")
public void Execute()
{
     // 1. select from DB - took 2 min
     // 2. call another API - took 10 min. <- here is when the postgres close my connection
    //  3. select from DB - throws exception.

}; 

What could be the correct design for it? We are using output for select from step 1 in API call and output of that API call used in step 3 for select. So these three steps are interdependent.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source