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.

CodePudding user response：

Ten minutes is a very long time to hold a transaction open. Your RDBMS server automatically disconnects your session, rolling back the transaction, because it cannot tell whether the transaction was started by an interactive (command-line) user who then went out to lunch without committing it, or by a long-running task like yours.

Open transactions can block other users of your RDBMS, so it's best to COMMIT them quickly.

Your best solution is to refactor your application code so it can begin, and then quickly commit, the transaction after the ten-minute response from that other API call.

It's hard to give you specific advice without knowing more. But you could set some sort of status = "API call in progress" column on a row before you call that slow API, and clear that status within your transaction after the API call completes.

Alternatively, you can set the transaction timeout for just that connection with something like this, to reduce the out-to-lunch risk on your system.

SET idle_in_transaction_session_timeout = '15m';