postgresql psycopg2.InterfaceError：连接已经关闭/ pgr_astar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20217571/
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
psycopg2.InterfaceError: connection already closed / pgr_astar
提问by Antonin
I am using psycopg2 to access a postgresql database from python. When I try to run the pgrouting function "pgr_astar" for the shortest path, I receive an error
我正在使用 psycopg2 从 python 访问 postgresql 数据库。当我尝试为最短路径运行 pgrouting 函数“pgr_astar”时，我收到一个错误
cur = db.cursor()
psycopg2.InterfaceError: connection already closed
Basically, what happened is: when pgr_astar does not find a path between two points, it creates a crash of the database, and it closes the connection to the database.
基本上，发生的事情是：当 pgr_astar 没有找到两点之间的路径时，它会导致数据库崩溃，并关闭与数据库的连接。
How is it possible to avoid this?
怎么可能避免这种情况？
I tried to isolate the function with a try/except and to create its own connection, so no problem if it closes:
我试图用 try/except 隔离该函数并创建自己的连接，所以如果它关闭就没有问题：
conn = psycopg2.connect(...)
try:
result = getRecords(conn,query_pgr_astar)
return float(result[0]['sum'])
except:
return 500000000.0
conn.close()
But in this case I receive a
但在这种情况下，我收到了
cur.execute(query)
psycopg2.DatabaseError: SSL SYSCALL error: EOF detected
How is it possible to manage impossible paths?
如何管理不可能的路径？
(I did the same with pgdb and the old shortest_path_astar function (not available to me anymore), and I had no problem)
（我对 pgdb 和旧的 shortest_path_astar 函数（我不再可用）做了同样的事情，我没有问题）
Postgresql log is:
Postgresql 日志是：
2013-11-27 15:54:35 CET LOG: terminating any other active server processes
2013-11-27 15:54:35 CET WARNING: terminating connection because of crash of another server process
2013-11-27 15:54:35 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2013-11-27 15:54:35 CET HINT: In a moment you should be able to reconnect to the database and repeat your command.
2013-11-27 15:54:35 CET WARNING: terminating connection because of crash of another server process
2013-11-27 15:54:35 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2013-11-27 15:54:35 CET HINT: In a moment you should be able to reconnect to the database and repeat your command.
2013-11-27 15:54:35 CET LOG: all server processes terminated; reinitializing
2013-11-27 15:54:35 CET LOG: database system was interrupted; last known up at 2013-11-27 15:42:01 CET
2013-11-27 15:54:35 CET LOG: database system was not properly shut down; automatic recovery in progress
2013-11-27 15:54:35 CET LOG: record with zero length at 65/80875D60
2013-11-27 15:54:35 CET LOG: redo is not required
2013-11-27 15:54:35 CET LOG: autovacuum launcher started
2013-11-27 15:54:35 CET LOG: database system is ready to accept connections
The query I do is:
我做的查询是：
SELECT SUM(t2.length) FROM (SELECT id2 FROM pgr_astar('SELECT edge_id AS id, vertex_id1 AS source, vertex_id2 AS target, ' || '(CASE WHEN door = ''S'' THEN -1.0 ELSE (length * (CASE network WHEN ''0'' THEN 1.0 WHEN ''10'' THEN 1.2 WHEN ''20'' THEN 1.5 WHEN ''30'' THEN 2.0 ELSE 1.0 END) * (CASE type WHEN ''9.1'' THEN CASE WHEN level1 < level2 THEN 3.0 ELSE 2.0 END WHEN ''9.2'' THEN CASE WHEN level1 < level2 THEN 15.0 ELSE 12.0 END ELSE 1.0 END) + (CASE type WHEN ''9.3'' THEN 40.0 ELSE 0.0 END) ) END)::float8 AS cost, ' || '(CASE WHEN door_rev = ''S'' THEN -1.0 ELSE (length * (CASE network WHEN ''0'' THEN 1.0 WHEN ''10'' THEN 1.2 WHEN ''20'' THEN 1.5 WHEN ''30'' THEN 2.0 ELSE 1.0 END) * (CASE type WHEN ''9.1'' THEN CASE WHEN level1 < level2 THEN 3.0 ELSE 2.0 END WHEN ''9.2'' THEN CASE WHEN level1 < level2 THEN 15.0 ELSE 12.0 END ELSE 1.0 END) + (CASE type WHEN ''9.3'' THEN 40.0 ELSE 0.0 END) ) END )::float8 AS reverse_cost, ' || 'x1, y1, x2, y2 FROM edges', " + str(destination_route) + ", " + str(origin_route) + ", TRUE, TRUE)) as t1, edges as t2 where t1.id2 = t2.edge_id
door = ''S'' means door is closed, and in this case, value is -1.
door = ''S'' 表示门已关闭，在这种情况下，值为 -1。
采纳答案by Stephen Woodbridge
I assume you can check your connection status in python (I don't use python) so you can recover and do something sensible, like reconnect. You might need to sleep for a couple of seconds to let the database recover from the crash before you reconnect.
我假设你可以在 python 中检查你的连接状态（我不使用 python），这样你就可以恢复并做一些明智的事情，比如重新连接。在重新连接之前，您可能需要休眠几秒钟，让数据库从崩溃中恢复。
Regarding the pgrouting issue, I would like to see a simple test case that can reproduce this problem in pgadmin or psql shell, filed as a bug so I can look into it. Our code should never crash the server, but sometimes it does and we need to fix those issues.
关于 pgrouting 问题，我希望看到一个可以在 pgadmin 或 psql shell 中重现此问题的简单测试用例，并将其作为错误提交，以便我进行调查。我们的代码永远不应该使服务器崩溃，但有时它会崩溃，我们需要解决这些问题。
