psycopg2 hangs with no timeout: connect, query and dead connections
The script hangs on psycopg2.connect() with no errorShort 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.
Fix it fast
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;"2Match what you see, then jump to its fix
- 1
psycopg2.connect() never returns when the host is unreachableNo connect timeout is setFix - 2
The connection is fine and one statement keeps running far beyond its normal timeA query runs far longer than it shouldFix - 3
The 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
A query on an open connection hangs forever after a network drop, VM freeze or failoverThe network path died and the client never noticedFix
- 1
What does the error look like?
The script hangs on psycopg2.connect() with no errorpsycopg2.errors.QueryCanceled (SQLSTATE 57014) after you set a statement timeoutA 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=*.py2Fix
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
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
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
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=*.py2Fix
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.pyConfirmed by postgresql.org
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:
- stegnor.comMy company site: 804 pages from a Python and Jinja generator, with 15 free browser tools.
- MenuloA restaurant discovery platform in Germany, kept current by a Python ingestion pipeline.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
psycopg2 fails with: server closed the connection unexpectedly
OperationalError: server closed the connection unexpectedly (psycopg2, Flask, SQLAlchemy)
The client was talking to Postgres and the connection vanished.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
psycopg2 cannot connect: connection refused on port 5432
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the address psycopg2 tried.
3 causes, each with a fixDatabases
psycopg2 cannot resolve the host name db in Docker Compose
could not translate host name "db" to address
Compose registers each service in its own DNS under the service name, but only containers on the same Compose network can look it up.
4 causes, each with a fix