Postgres SSL SYSCALL 错误：使用 python 和 psycopg 检测到 EOF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24130305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres SSL SYSCALL error: EOF detected with python and psycopg
提问by Phil Donovan
Using psycopg2 package with python 2.7 I keep getting the titled error: psycopg2.DatabaseError: SSL SYSCALL error: EOF detected
在 python 2.7 中使用 psycopg2 包我不断收到标题错误：psycopg2.DatabaseError：SSL SYSCALL 错误：检测到 EOF
It only occurs when I add a WHERE column LIKE ''%X%''clause to my pgrouting query. An example:
它仅在我WHERE column LIKE ''%X%''向 pgrouting 查询添加子句时发生。一个例子：
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)
Threads on the internet suggest it is an issue with SSL intuitively, but whenever I comment out the pattern matching side of things the query and connection to the database works fine.
互联网上的线程直观地表明这是 SSL 的问题，但是每当我注释掉事物的模式匹配方面时，查询和到数据库的连接都可以正常工作。
This is on a local database running Xubuntu 13.10.
这是在运行 Xubuntu 13.10 的本地数据库上。
After further investigation: It looks like this may be cause by the pgrouting extension crashing the database because it is a bad query and their are not links which have this pattern.
经过进一步调查：看起来这可能是由于 pgrouting 扩展使数据库崩溃造成的，因为这是一个错误的查询，并且它们不是具有这种模式的链接。
Will post an answer soon ...
将尽快发布答案...
回答by piro
You may need to express %as %%because %is the placeholder marker. http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
您可能需要表达%as%%因为%是占位符标记。http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
回答by antonagestam
I ran into this problem when running a slow query in a Droplet on a Digital Ocean instance. All other SQL would run fine and it worked on my laptop. After scaling up to a 1 GB RAM instance instead of 512 MB it works fine so it seems that this error could occur if the process is running out of memory.
我在 Digital Ocean 实例上的 Droplet 中运行慢查询时遇到了这个问题。所有其他 SQL 都可以正常运行，并且可以在我的笔记本电脑上运行。在扩展到 1 GB RAM 实例而不是 512 MB 之后，它工作正常，因此如果进程内存不足，似乎可能会发生此错误。
回答by Fiskabollen
I got this error running a large UPDATE statement on a 3 million row table. In my case it turned out the disk was full. Once I had added more space the UPDATE worked fine.
我在 300 万行表上运行大型 UPDATE 语句时遇到此错误。就我而言，事实证明磁盘已满。一旦我添加了更多空间，UPDATE 就可以正常工作。
回答by FoxMulder900
This issue occurred for me when I had some rogue queries running causing tables to be locked indefinitely. I was able to see the queries by running:
当我运行一些恶意查询导致表被无限期锁定时，我就出现了这个问题。我能够通过运行查看查询：
SELECT * from STV_RECENTS where status='Running' order by starttime desc;
then kill them with:
然后用以下方法杀死它们：
SELECT pg_terminate_backend(<pid>);
回答by Charles F
Very similar answer to what @FoxMulder900 did, except I could not get his first select to work. This works, though:
与@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;
If you want to kill the processes from long_runningjust comment out the last line and insert SELECT pg_cancel_backend(long_running.pid) from long_running ;
如果您想终止进程，long_running只需注释掉最后一行并插入SELECT pg_cancel_backend(long_running.pid) from long_running ;
回答by papko26
In my case that was OOM killer (query is too heavy)
在我的情况下，这是 OOM 杀手（查询太重）
Check dmesg:
检查 dmesg：
dmesg | grep -A2 Kill
In my case:
就我而言：
Out of memory: Kill process 28715 (postgres) score 150 or sacrifice child
