Understanding the `Terminating connection due to idle-in-transaction timeout` error

If you are getting idle-in-transaction timeout error in a Postgres DB, it means that idle_in_transaction_session_timeout is set to a value other than 0, which disables the timeout check by default.

The name `idle_in_transaction_session_timeout` is descriptive enough. It means you started a transaction and did not do anything in the timeout duration. Therefore, it thinks that transaction is idle. That also means that consecutive query execution with the transaction must be within the timeout.

You can see more information in the Postgres documentation.

20.11. Client Connection Defaults

With the following docker container, we create a Postgres DB and set `idle_in_transaction_session_timeout` to 3 seconds.

version: '3.8'

services:
  postgres:
    image: postgres:latest
    restart: always
    environment:
      POSTGRES_DB: mydatabase
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    ports:
      - "5432:5432"
    volumes:
      - ./init-scripts:/docker-entrypoint-initdb.d  # Mount folder for initialization scripts
    entrypoint: >
      bash -c '
      echo "Initializing PostgreSQL...";
      echo "ALTER DATABASE mydatabase SET idle_in_transaction_session_timeout = '\''3s'\'';" > /docker-entrypoint-initdb.d/01-alter-db.sql;
      exec docker-entrypoint.sh postgres;
      '

Following Go program, get sleep time as argument and sleeps after transaction is initiated.

package main

import (
 "context"
 "fmt"
 "log"
 "os"
 "strconv"
 "time"

 _ "github.com/jackc/pgx/v4"
 "github.com/jackc/pgx/v4/pgxpool"
)

func main() {
 // Ensure sleep time is passed as an argument
 if len(os.Args) != 2 {
  log.Fatalf("Usage: %s <sleep_time_in_seconds>\n", os.Args[0])
 }
 sleepTime, err := strconv.Atoi(os.Args[1])
 if err != nil || sleepTime < 0 {
  log.Fatalf("Invalid sleep time: %s\n", os.Args[1])
 }

 // Define PostgreSQL connection parameters
 connStr := "postgres://myuser:mypassword@localhost:5432/mydatabase"

 // Create a connection pool
 pool, err := pgxpool.Connect(context.Background(), connStr)
 if err != nil {
  log.Fatalf("Unable to connect to database: %v\n", err)
 }
 defer pool.Close()

 fmt.Println("Connected to the database successfully!")

 // Start a transaction
 tx, err := pool.Begin(context.Background())
 if err != nil {
  log.Fatalf("Failed to begin transaction: %v\n", err)
 }

 // Defer a rollback in case anything goes wrong
 defer tx.Rollback(context.Background())

 // Sleep for the provided duration
 time.Sleep(time.Duration(sleepTime) * time.Second)
 fmt.Printf("Sleeping for %d seconds to mimic transaction is idle...\n", sleepTime)

 // Create a table inside the transaction
 createTableQuery := `
 CREATE TABLE IF NOT EXISTS example_table (
  id SERIAL PRIMARY KEY,
  name TEXT
 );`
 _, err = tx.Exec(context.Background(), createTableQuery)
 if err != nil {
  log.Fatalf("Failed to create table: %v\n", err)
 }

 fmt.Printf("Table created.\n")

 // Commit the transaction
 err = tx.Commit(context.Background())
 if err != nil {
  log.Fatalf("Failed to commit transaction: %v\n", err)
 }

 fmt.Println("Transaction committed successfully!")
}

Remember that idle time was 3 seconds. First, we will run the app with argument 2 to sleep 2 seconds. We expect that it should succeed.

go run main.go 2

It outputs:

Connected to the database successfully!
Sleeping for 2 seconds to mimic transaction is idle...
Table created.
Transaction committed successfully!

Now we will execute same application with a greater value that 3 seconds:

go run main.go 4

It outputs:

Connected to the database successfully!
Sleeping for 4 seconds to mimic transaction is idle...
2024/09/08 23:02:01 Failed to create table: FATAL: terminating connection due to idle-in-transaction timeout (SQLSTATE 25P03)
exit status 1

Remarks:

You can get this error; for example, if you start a transaction and between query executions you make HTTP calls or time heavy calculations that last more than the timeout duration.

Another problem that may arise from the timeout is that if you use any tool like DBeaver and your queries takes more than the duration, it may always ask to start new transaction again and again.

How to change it:

You can increase the duration with the following query:

-- to change it in DB level
ALTER DATABASE mydatabase SET idle_in_transaction_session_timeout = '3s';
-- to change it in role level
ALTER ROLE role_abc SET idle_in_transaction_session_timeout = '10min';

If you set it to 0, it will be disabled.