Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

psycopg2 hangs with no timeout: connect, query and dead connections

ErrorThe script hangs on psycopg2.connect() with no error

Short answer

Neither psycopg2 nor PostgreSQL sets a time limit by default, so a stuck connect, a slow query, a lock wait or a silently dead network link can wait forever. Each has its own setting: connect_timeout, statement_timeout, lock_timeout and tcp_user_timeout.

  • Applies to psycopg2 2.9, PostgreSQL 17, Python 3.13
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find out which stage hangs: the connect, a running query, a wait for a lock, or a dead link. Print where the script stops, then check which timeouts the server already applies.

    psql -U postgres -c "SHOW statement_timeout;" -c "SHOW lock_timeout;"
  2. 2Match what you see, then jump to its fix

    1. 1psycopg2.connect() never returns when the host is unreachableNo connect timeout is setFix
    2. 2The connection is fine and one statement keeps running far beyond its normal timeA query runs far longer than it shouldFix
    3. 3The statement does nothing for a long time while another session has the same table or row open in a transactionThe statement waits for a lock held by another sessionFix
    4. 4A query on an open connection hangs forever after a network drop, VM freeze or failoverThe network path died and the client never noticedFix

What does the error look like?

  • The script hangs on psycopg2.connect() with no error
  • psycopg2.errors.QueryCanceled (SQLSTATE 57014) after you set a statement timeout
  • A query blocks for minutes while another session holds a lock

Cause 1 of 4

No connect timeout is set

1Check

libpq's connect_timeout is in seconds, and zero, negative or unset means wait indefinitely. It applies separately to each host name or IP address you list, so a list of two hosts can wait up to twice as long in total.

grep -rn "connect(" . --include=*.py

2Fix

Pass connect_timeout in the connection string or as a keyword.

import psycopg2

conn = psycopg2.connect(
    "postgresql://app:secret@db.example.com:5432/appdb?connect_timeout=10"
)

3Verify

Point it at an address that does not answer; it now fails after about ten seconds.

python -c "import psycopg2; psycopg2.connect(host='203.0.113.1', connect_timeout=5)"

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 4

A query runs far longer than it should

1Check

statement_timeout aborts any statement that runs longer than the value, in milliseconds when no unit is given. Zero, the default, disables it. The PostgreSQL docs advise against setting it in postgresql.conf because that affects every session.

psql -U postgres -c "SHOW statement_timeout;"

2Fix

Set it for this session only, through the options connection parameter, so a runaway statement is cancelled and raises QueryCanceled.

import psycopg2

conn = psycopg2.connect(
    host="db.example.com", dbname="appdb", user="app", password="secret",
    options="-c statement_timeout=5000",
)

3Verify

Run a deliberately slow statement and expect QueryCanceled after five seconds.

cur = conn.cursor()
cur.execute("SELECT pg_sleep(10)")

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 4

The statement waits for a lock held by another session

1Check

lock_timeout aborts a statement that waits longer than the value while trying to get a lock on a table, row or other object. Zero, the default, disables it, and the PostgreSQL docs advise against setting it globally.

psql -U postgres -c "SHOW lock_timeout;"

2Fix

Set lock_timeout for the session next to statement_timeout, and fix the code that holds locks too long (long transactions).

options="-c statement_timeout=5000 -c lock_timeout=2000"

3Verify

With a second session holding the lock, your statement now fails after two seconds.

psql -U postgres -c "SHOW lock_timeout;"

Confirmed by postgresql.org

Did this fix it?

Cause 4 of 4

The network path died and the client never noticed

1Check

tcp_user_timeout is the number of milliseconds sent data may stay unacknowledged before the connection is forcibly closed. Zero uses the system default. keepalives_idle is in seconds and a zero uses the system default as well.

grep -rn "tcp_user_timeout\|keepalives" . --include=*.py

2Fix

Set tcp_user_timeout, and keepalive parameters for idle connections. Both are ignored on Unix-domain socket connections.

import psycopg2

conn = psycopg2.connect(
    host="db.example.com", dbname="appdb", user="app", password="secret",
    connect_timeout=10, tcp_user_timeout=20000,
    keepalives=1, keepalives_idle=30,
)

3Verify

After cutting the network during a query, the call fails in about twenty seconds instead of hanging.

python app.py

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Give every new connection a connect_timeout and a session statement_timeout.
  • Catch psycopg2.errors.QueryCanceled and decide whether to retry or report.
  • Keep transactions short so locks do not pile up.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.