Psycopg2 db connection hangs on lost network connection

Question

Problem description

I'm using psycopg2 to connect to my PostgreSQL database on a remote host. I open a connection and wait for requests, then for each request I run queries on the connection and return data.

But when the network connection is lost after the connection is already open the next db query hangs and I have to kill the program manually.

Details:

• it hangs for at least 2 hours (I couldn't wait longer)
• the "network down" situation is actually VPN going down (db host is accesible only by VPN)
• I can't use asynchronous connection because I need transactions
• python 2.6
• psycopg 2.2.1
• debian linux 6 and 7, 64bit
• postgresql 8.4 and 9.1

What I want/need

I need some reliable way to detect a failed connection before running a query, so my program won't hang, or a way to make cursor.execute(..) raise an exception on failed connection.

Example:

import psycopg2
import time

conn = psycopg2.connect("host='dbs' dbname='foo' user='joe' password='x'")
time.sleep(10) # I manually turn VPN off during this sleep..
cu = conn.cursor()
cu.execute('SELECT 1') # <- hangs here
print cu.fetchone()
cu.commit()

What have I tried (and what didn't work):

• setting TCP timeout "globally" - before psycopg2 import, I added:

  import socket
  socket.setdefaulttimeout(10)
  

• setting TCP timeout on psycopg.connection's socket:

  ..
  conn = psycopg2.connect(...
  s = socket.fromfd(conn.fileno(), socket.AF_INET, socket.SOCK_STREAM)
  s.settimeout(5)
  ..
  

• enabling keepalive for psycopg.connection's socket:

  ...
  conn = psycopg2.connect(...
  s = socket.fromfd(conn.fileno(), socket.AF_INET, socket.SOCK_STREAM)
  s.settimeout(5)
  s.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
  s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPIDLE, 1)
  s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, 3)
  s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, 5)
  ...
  

Answer 1

After a long and brutal struggle, I think I fixed this issue by simply doing the strategy others are talking about, but using the psycopg2 connect function itself:

from psycopg2 import connect


conn = connect(
        database=database,
        user=username,
        password=password,
        host=hostname,
        port=port,
        connect_timeout=3,
        # https://www.postgresql.org/docs/9.3/libpq-connect.html
        keepalives=1,
        keepalives_idle=5,
        keepalives_interval=2,
        keepalives_count=2)

I was seeing psycopg2 hang consistently on long-running queries, but now the issue seems to be fully resolved.

Note this may be new functionality, since this question is old.