Psycopg2 数据库连接在网络断开时挂起

Question

问题描述

我正在使用psycopg2来连接远程的PostgreSQL数据库。我打开一个连接，等待请求，然后对每个请求在这个连接上运行查询并返回数据。

但是，当网络连接在已经打开的连接后断开时，下一次数据库查询就会卡住，我必须手动结束程序。

具体情况：

• 卡住至少2个小时（我没法等更久）
• “网络断开”的情况实际上是VPN掉线（数据库主机只能通过VPN访问）
• 我不能使用异步连接，因为我需要事务处理
• 使用的是python 2.6
• psycopg版本是2.2.1
• 操作系统是debian linux 6和7，64位
• postgresql版本是8.4和9.1

我想要/需要的

我需要一种可靠的方法来检测连接失败，在运行查询之前，这样我的程序就不会卡住，或者有办法让cursor.execute(..)在连接失败时抛出异常。

示例：

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()

我尝试过的（以及没有成功的）：

• 在导入psycopg2之前，设置TCP超时“全局” - 我添加了：

  import socket
  socket.setdefaulttimeout(10)
  

• 在psycopg.connection的套接字上设置TCP超时：

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

• 为psycopg.connection的套接字启用keepalive：

  ...
  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

我查看了一下套接字的超时时间，读了这个和这个，这些设置对我来说有效。

s = socket.fromfd(connection.fileno(),
                  socket.AF_INET, socket.SOCK_STREAM)
# Enable sending of keep-alive messages
s.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
# Time the connection needs to remain idle before start sending
# keepalive probes
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPIDLE, int(ceil(time)))
# Time between individual keepalive probes
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, 1)
# The maximum number of keepalive probes should send before dropping
# the connection
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, 3)

Answer 2

为了确保连接仍然有效，可以查看一个叫做 connection.isolation_level 的属性。如果连接已经断开，这个操作会引发一个错误，错误类型是 OperationalError，并且错误代码是 pgcode == "57P01"。

try: connection.isolation_level except OperationalError as oe: conn = psycopg2.connect(dsn)

Answer 3

OP和Gabriel Salla提供的关于配置KEEPALIVE的解决方案并不完整。这个方案只有在连接空闲的时候（也就是在网络断开之前没有发送任何数据）才有效。

如果在网络已经断开但KEEPALIVE功能还没有检测到的情况下，已经发送了一些数据，那么就会出现卡住的情况。这是因为在发送数据时，使用的是RTO机制，而不是KEEPALIVE。

要为RTO设置超时时间，你需要为套接字设置TCP_USER_TIMEOUT超时时间（单位是毫秒）。

完整的解决方案是（将KEEPALIVE和RTO的超时时间都设置为10秒）：

s = socket.fromfd(conn.fileno(), socket.AF_INET, socket.SOCK_STREAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPIDLE, 6)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, 2)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, 2)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_USER_TIMEOUT, 10000)

Answer 4

经过一番艰苦的努力，我觉得我解决了这个问题，方法就是采用了大家提到的策略，但我直接使用了psycopg2的连接函数：

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)

之前我发现psycopg2在处理一些长时间运行的查询时总是卡住，但现在这个问题似乎完全解决了。

需要注意的是，这可能是新功能，因为这个问题提出来的时间比较久了。