python - psycopg2 + pgbouncer。带有 gevent 错误的异步模式

Question

我有一个应用程序 psycopg2 + pgbouncer + gevent。异步应用程序，即一个进程服务于多个请求。对数据库的异步访问出现在最新版本的 psycopg2 中，如果不是 2.2。但是就在这个版本中引入了一个错误，它与 pgbouncer 有很多断开连接。在 pgbouncer 日志中有条目:

2011-10-04 12:16:38.972 4590 LOG C-0x1b3f490: database/user@10.58.65.143:43849 login successful: db=database user=user
2011-10-04 12:16:38.972 4590 LOG C-0x1b3f0a0: database/user@10.58.65.143:43850 login successful: db=database user=user
2011-10-04 12:16:38.973 4590 LOG C-0x1b40840: database/user@10.58.65.143:43802 closing because: client close request (age=0)
2011-10-04 12:16:38.973 4590 LOG S-0x1b5cd80: database/user@127.0.0.1:5432 closing because: unclean server (age=0)
2011-10-04 12:16:38.976 4590 LOG C-0x1b3ef50: database/user@10.58.65.143:43825 closing because: client close request (age=0)
2011-10-04 12:16:38.976 4590 LOG S-0x1b5e520: database/user@127.0.0.1:5432 closing because: unclean server (age=0)
2011-10-04 12:16:38.977 4590 LOG S-0x1b5e520: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.979 4590 LOG C-0x1b3f340: database/user@10.58.65.143:43791 closing because: client close request (age=0)
2011-10-04 12:16:38.979 4590 LOG S-0x1b5d410: database/user@127.0.0.1:5432 closing because: unclean server (age=0)
2011-10-04 12:16:38.980 4590 LOG S-0x1b5d410: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.983 4590 LOG S-0x1b5cd80: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.987 4590 LOG S-0x1b5dbf0: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.990 4590 LOG S-0x1b5c1b0: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.992 4590 LOG C-0x1b3ff10: database/user@10.58.65.143:43854 login successful: db=database user=user
2011-10-04 12:16:38.995 4590 LOG S-0x1b5c450: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.996 4590 LOG C-0x1b3f340: database/user@10.58.65.143:43855 login successful: db=database user=user
2011-10-04 12:16:38.996 4590 LOG C-0x1b3ee00: database/user@10.58.65.143:43828 closing because: client close request (age=0)
2011-10-04 12:16:38.998 4590 LOG S-0x1b5c300: database/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:38.999 4590 LOG C-0x1b3f1f0: database/user@10.58.65.143:43805 closing because: client close request (age=0)
2011-10-04 12:16:38.999 4590 LOG S-0x1b5daa0: database/user@127.0.0.1:5432 closing because: unclean server (age=0)
2011-10-04 12:16:39.010 4590 LOG C-0x1b41410: database/user@10.58.65.143:43655 closing because: client close request (age=1)
2011-10-04 12:16:39.010 4590 LOG S-0x1b5d2c0: database/user@127.0.0.1:5432 closing because: unclean server (age=1)
2011-10-04 12:16:39.011 4590 LOG S-0x1b5d2c0: datab+ase/user@127.0.0.1:5432 new connection to server
2011-10-04 12:16:39.014 4590 LOG C-0x1b3fc70: database/user@10.58.65.143:43818 closing because: client close request (age=0)
2011-10-04 12:16:39.014 4590 LOG S-0x1b5d170: database/user@127.0.0.1:5432 closing because: unclean server (age=0)
2011-10-04 12:16:39.042 4590 LOG C-0x1b40990: database/user@10.58.65.143:43822 closing because: client close request (age=0)
2011-10-04 12:16:39.042 4590 LOG S-0x1b5de90: database/user@127.0.0.1:5432 closing because: unclean server (age=0)
2011-10-04 12:16:39.043 4590 LOG C-0x1b40060: database/user@10.58.65.143:43820 closing because: client close request (age=0)
2011-10-04 12:16:39.043 4590 LOG S-0x1b5e280: database/user@127.0.0.1:5432 closing because: unclean server (age=0)

也许有一些方法可以配置 pgbouncer，以避免有关服务器不干净的消息？ Information about a bug ，就像有一个补丁可以治愈某些东西。来自 ubuntu 存储库的所有包，在生产机器上应用补丁——不是一个好的选择。我的配置 pgboucner:

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql

pool_mode = session

server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10

max_client_conn = 1000
default_pool_size = 200

log_connections = 1
log_disconnections = 1

Answer 1

您可能没有调用 connection.close()。在 Psycopg 2.2 之前，连接删除时会发出隐式回滚，但不再能保证这种情况(甚至在它被认为不安全之前)。

参见 http://initd.org/psycopg/docs/connection.html#connection.close