pgbouncer - 关闭，因为：每个连接上的不洁服务器 [英] pgbouncer - closing because: unclean server on every connection

问题描述

我正在运行Django 1.3与PostgreSQL 9.1 / PostGIS 1.5，psycopg2 2.4.2和pgbouncer 1.4.2。

在数据库的每一个连接上我得到pgbouncer.log中的日志条目：

2011-11-20 02：15：25.027 29538 LOG S-0x96c2200：app_db / postgres @ 192.168.171.185:5432关闭，因为：unclean server（age = 0）。

我找不到任何解决方案的问题 - 任何人都有一个想法为什么？我已经尝试重新配置pgbouncer（会话/事务模式，不同的超时等），但无济于事。

解决方案

认为我已经弄清楚了。问题在于Django和Psycopg2长期存在的问题。基本上，Psycopg2会自动向DB发出一个BEGIN语句。但是，如果Django认为没有发生数据修改，则在交易结束时不会发出COMMIT。

这个问题有几个解决方案，看看 http://www.slideshare.net/OReillyOSCON/unbreaking-your-django应用了解更多详情。理想情况下，您关闭自动提交（通过在数据库设置中设置autocommit = True，尴尬的命名约定）。这样可以防止只读函数上的事务，还可以阻止写入函数，因此您需要在@commit_on_success装饰器中手动包装这些函数。

或者，只需添加django即可。 middleware.transaction.TransactionMiddleware到您的中间件类。这将在事务中包装每个请求。这意味着也不必要地在一个事务中包装只读请求，但这是一个快速而肮脏的解决方案。

I'm running Django 1.3 with PostgreSQL 9.1/PostGIS 1.5, psycopg2 2.4.2 and pgbouncer 1.4.2.

On every single connection to the database I get a log entry in pgbouncer.log:

2011-11-20 02:15:25.027 29538 LOG S-0x96c2200: app_db/postgres@192.168.171.185:5432 closing because: unclean server (age=0).

I can't find any solution to this problem - anybody have an idea why? I've tried reconfiguring pgbouncer (session/transaction mode, different timeouts etc), but to no avail.

解决方案

Ok, I think I've figured this out. The problem lies with a long-standing issue with Django and Psycopg2. Basically, Psycopg2 will automatically issue a BEGIN statement to the DB. However, if Django thinks no data-modification has occurred, it won't issue a COMMIT at the end of a transaction.

There are a few solutions to this problem, look at http://www.slideshare.net/OReillyOSCON/unbreaking-your-django-application for more details. Ideally you turn off automatic commits (by setting autocommit = True in your DB settings, awkward naming convention). This prevents transactions on read-only functions, but also on write functions so you need to manually wrap those functions in a @commit_on_success decorator.

Alternatively, just add the django.middleware.transaction.TransactionMiddleware to your Middleware classes. This will wrap every request in a transaction. This means also unnecessarily wrapping read-only requests in a transaction, but it's a quick-and-dirty solution.

这篇关于pgbouncer - 关闭，因为：每个连接上的不洁服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！