python - psycopg2.InterfaceError : connection already closed/pgr_astar

Question

我正在使用 psycopg2 从 python 访问 postgresql 数据库。当我尝试为最短路径运行 pgrouting 函数“pgr_astar”时，我收到错误

cur = db.cursor()
psycopg2.InterfaceError: connection already closed

基本上，发生的事情是:当 pgr_astar 找不到两点之间的路径时，它会导致数据库崩溃，并关闭与数据库的连接。

如何避免这种情况？

我尝试用 try/except 隔离该函数并创建它自己的连接，所以即使它关闭也没有问题:

conn = psycopg2.connect(...)
try:
    result = getRecords(conn,query_pgr_astar)
    return float(result[0]['sum'])
except:
    return 500000000.0
conn.close()

但在这种情况下我收到一个

cur.execute(query)
psycopg2.DatabaseError: SSL SYSCALL error: EOF detected

如何管理不可能的路径？

(我对 pgdb 和旧的 shortest_path_astar 函数(我不再可用)做了同样的事情，我没有问题)

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

我做的查询是:

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'' 表示门已关闭，在本例中，值为-1。

Answer 1

我假设您可以在 python 中检查您的连接状态(我不使用 python)，这样您就可以恢复并做一些明智的事情，比如重新连接。在重新连接之前，您可能需要休眠几秒钟，让数据库从崩溃中恢复过来。

关于 pgrouting 问题，我希望看到一个简单的测试用例，它可以在 pgadmin 或 psql shell 中重现这个问题，作为一个错误提交，以便我可以调查它。我们的代码不应该让服务器崩溃，但有时它会崩溃，我们需要修复这些问题。