Django does not close persistent connections on server shutdown

[Casper van der Wel]

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).

[Mariusz Felisiak]

I think this is already documented, see ​"Transaction pooling and server-side cursors". Can you take a look?

[Mariusz Felisiak]

Also, Django 2.2 doesn't receive bugfixes anymore. Can you reproduce this issue with Django 3.1+?