Psycopg / Postgres：连接随机闲逛 [英] Psycopg / Postgres : Connections hang out randomly

问题描述

我正在将psycopg2用于我目前正在使用的cherrypy应用程序以及cli& phpgadmin手动处理一些操作。这是python代码：

I'm using psycopg2 for the cherrypy app I'm currently working on and cli & phpgadmin to handle some operations manually. Here's the python code :

#One connection per thread
cherrypy.thread_data.pgconn = psycopg2.connect("...") 
...
#Later, an object is created by a thread :
class dbobj(object):
 def __init__(self):
  self.connection=cherrypy.thread_data.pgconn
  self.curs=self.connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
...
#Then,
try:
 blabla
 self.curs.execute(...)
 self.connection.commit()
except:
 self.connection.rollback()
 lalala
...
#Finally, the destructor is called :
def __del__(self):
 self.curs.close()

我在使用psycopg或postgres时遇到了问题（尽管我认为后者更有可能）。发送一些查询后，我的连接断开。同样，phpgadmin-通常-也被删除；多次提出请求后，它提示我重新连接。只有CLI才能保持持久状态。

I'm having a problem with either psycopg or postgres (altough I think the latter is more likely). After having sent a few queries, my connections drop dead. Similarly, phpgadmin -usually- gets dropped as well ; it prompts me to reconnect after having made requests several times. Only the CLI remains persistent.

问题是，这些情况非常随机发生，我什至无法找出原因。请求几页后，我要么被锁定，要么请求数百页后再也没有遇到任何问题。终止应用程序后，我在postgres日志中发现的唯一错误是：

The problem is, these happen very randomly and I can't even track down what the cause is. I can either get locked down after a few page requests or never really encounter anything after having requested hundreds of pages. The only error I've found in postgres log, after terminating the app is :

...
LOG:  unexpected EOF on client connection
LOG:  could not send data to client: Broken pipe
LOG:  unexpected EOF on client connection
...

我想每次创建新的dbobj实例时都创建一个新连接，但我绝对不想这样做。

I thought of creating a new connection every time a new dbobj instance is created but I absolutely don't want to do this.

此外，我读到除非提交所有事务，否则可能会遇到类似的问题：对于每个单个INSERT / UPDATE查询，我都使用try / except块，但是我从不将其用于SELECT查询，也不想编写更多样板代码（顺便说一句，是否需要提交？）。即使是这样，为什么phpgadmin也要关闭？

Also, I've read that one may run into similar problems unless all transactions are committed : I use the try/except block for every single INSERT/UPDATE query, but I never use it for SELECT queries nor do I want to write even more boilerplate code (btw, do they need to be committed ?). Even if that's the case, why would phpgadmin close down ?

在.conf文件中将max_connections设置为100，所以我也不认为这是原因。单个cherrypy工作者只有10个线程。

max_connections is set to 100 in the .conf file, so I don't think that's the reason either. A single cherrypy worker has only 10 threads.

任何人都知道我应该先看哪里吗？

Does anyone have an idea where I should look first ?

推荐答案

即使我不知道为什么成功的 SELECT 查询也会阻止连接，从而导致 .commit（ ）在几乎不必与另一个查询一起使用的每个查询之后都解决了该问题。

Even though I don't have any idea why successful SELECT queries block the connection, spilling .commit() after pretty much every single query that doesn't have to work in conjunction with another solved the problem.

这篇关于Psycopg / Postgres：连接随机闲逛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！