Postgres SSL SYSCALL错误：与python和psycopg检测到EOF

Question

在使用psycopg2这个包和Python 2.7时，我总是遇到一个错误：psycopg2.DatabaseError: SSL SYSCALL error: EOF detected。

这个错误只在我给pgrouting查询添加了一个WHERE column LIKE ''%X%''的条件时出现。举个例子：

SELECT id1 as node, cost FROM PGR_Driving_Distance(
  'SELECT id, source, target, cost 
     FROM edge_table
     WHERE cost IS NOT NULL and column LIKE ''%x%'' ',
  1, 10, false, false)

网上的讨论都说这可能是SSL的问题，但每当我把这个模式匹配的部分注释掉时，查询和数据库连接就正常了。

我是在运行Xubuntu 13.10的本地数据库上遇到这个问题的。

经过进一步调查：看起来这个问题可能是因为pgrouting扩展导致数据库崩溃，因为这个查询有问题，并且没有链接符合这个模式。

我会尽快发布一个答案……

Answer 1

这个回答和@FoxMulder900的很像，不过我没能让他第一个选择的部分正常工作。不过这个方法是有效的：

WITH long_running AS (
    SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '1 minutes'
      and state = 'active'
)
SELECT * from long_running;

如果你想结束来自 long_running 的进程，只需要把最后一行注释掉，然后插入 SELECT pg_cancel_backend(long_running.pid) from long_running ; 这行代码。

Answer 2

这个问题发生在我有一些不正常的查询在运行，导致数据库表被锁住了，锁定时间没有限制。我通过运行以下命令可以看到这些查询：

SELECT * from STV_RECENTS where status='Running' order by starttime desc;

然后用下面的命令把它们结束掉：

SELECT pg_terminate_backend(<pid>);

Answer 3

我在Digital Ocean的一个Droplet上运行一个慢查询时遇到了这个问题。其他所有的SQL查询都运行得很好，而且在我的笔记本电脑上也没问题。当我把内存从512 MB升级到1 GB时，这个问题就解决了。所以看起来这个错误可能是因为程序内存不够用导致的。

Answer 4

我遇到了同样的错误。我的CPU和内存使用情况都正常，但@antonagestam提供的解决方案对我没用。

问题主要出现在创建引擎的步骤。使用 pool_pre_ping=True 解决了这个问题：

engine = sqlalchemy.create_engine(connection_string, pool_pre_ping=True)

这个设置的作用是，每次使用连接时，它会发送一个 SELECT 1 的查询来检查连接是否正常。如果连接失败，它就会重新连接并再次检查。成功后，才会执行实际的查询。

关于 pool_pre_ping 的 sqlachemy 文档

在我的情况下，我在Python的日志中也看到了同样的错误。我查看了 /var/log/postgresql/ 里的日志文件，发现有很多错误信息，比如 could not receive data from client: Connection reset by peer 和 unexpected EOF on client connection with an open transaction。这些问题可能是由于网络故障引起的。

Answer 5

错误信息：psycopg2.operationalerror: SSL SYSCALL error: EOF detected

环境设置：Airflow + Redshift + psycopg2

发生时间：当查询执行时间很长（超过300秒）时。

在这种情况下，会出现套接字超时。解决这个特定错误的方法是给连接字符串添加保持活动的参数。

keepalive_kwargs = {
    "keepalives": 1,
    "keepalives_idle": 30,
    "keepalives_interval": 5,
    "keepalives_count": 5,
}

conection = psycopg2.connect(connection_string, **keepalive_kwargs)

Redshift要求keepalives_idle的值要小于300。我使用的是30这个值，效果不错，但你可能需要根据自己的情况调整。也有可能只需要设置keepalives_idle这个参数，但要确保keepalives设置为1。

这是关于Postgres保持活动的文档链接。

这是关于Airflow建议300秒超时的文档链接。