id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32257	Django does not close persistent connections on server shutdown	Casper van der Wel	nobody	"Dear Django developers,

We ran into this issue while implementing PgBouncer between our gunicorn/Django webserver and PostGRES databases.

There are a lot of details here, I'll try to include them all but first a summary:

PgBouncer is a connection pooler for postgres. We use it in transaction mode, which means that pgbouncer-database are reused after a transaction ends. It makes sense to keep the connections between Django and pgbouncer open, and you do this by setting `DATABASES[""default""][""CONN_MAX_AGE""] = None`.

If you end the Django process with open database connections, you get ""client unexpected eof"" logs at the pgbouncer side. For us, this has bad consequences: pgbouncer does not always close the pgbouncer-server connection after a client disconnected unexpectedly, leading to problems for subsequent client connections. These have to deal with the fact that the server is halfway a transaction. Sometimes they get query results of previous queries. We saw all kinds of surprising things.

The way we solved it now is making use of a gunicorn hook `worker_int`. At worker shutdown, connections are closed. This works for us. Similarly, for celery, you have the `worker_process_shutdown` signal.

This is partially a pgbouncer problem, but still, Django should cleanup persistent close connections if it can, or at least mention that this issue may occur when using persistent connections in the documentation.

I believe this has to do with how postgresql sets up connections, but I didn't dive too much into that. They do mention pgbouncer explicitly in the docs, that since psycopg 2.2 they do not issue a ROLLBACK on a connection close. I am not sure if that is related; the issue here is also at play when outside of a transaction (we use the default autocommit=True)

The issue can be reproduced as follows:

- set up gunicorn/django, pgbouncer, postgres (I may be able to curate a docker-compose if necessary)
- use `""CONN_MAX_AGE""=None` in the database setting
- follow the pgbouncer logs
- start gunicorn
- send a request that opens a connection
- terminate gunicorn (Ctrl+C or SIGTERM to the master process)
- you should see a ""client unexpected eof"" in the pgbouncer logs

The fix is as follows, in the gunicorn config file:


{{{
def worker_int(worker):
    worker.log.info(""Worker is terminating, closing database connections"")

    # close all database connections
    from django.db import connections

    connections.close_all()
}}}


Versions:
- Ubuntu 18.04 with libpq-dev=12.4
- Django=2.2.17
- gunicorn=20.0.4
- psycopg2=2.8.6
- pgbouncer=1.14.0
- postgres=11.2

Rather surprisingly I could not find any ticket mentioning this issue, there are some about persistent connections, but none related to server shutdown (that I could find).
"	Bug	closed	Database layer (models, ORM)	2.2	Normal	needsinfo	postgres, database	François Freitag	Unreviewed	0	0	0	0	0	0