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
17887	"postgresql_psycopg2 sometimes leaves connections ""idle in transaction"""	Brodie Rao	Aymeric Augustin	"When using the postgresql_psycopg2 backend with DB-level autocommit ''disabled'', long-running Django processes (e.g., under Gunicorn) can leave around idle connections stuck in transactions, making things like schema migrations difficult to impossible to do without killing those connections.

This happens because by default (with autocommit disabled), psycopg2 issues a `BEGIN` before the connection's first query is run. If the connection is committed or rolled back, it'll issue another `BEGIN` before the next query. Unless you commit or roll back the connection, it'll stick around in that transaction indefinitely.

To make matters worse, if the connection is closed while it's in this idle state, it can cause problems with pgbouncer. pgbouncer will see there's a transaction in progress but that the client disconnected. The only way it can recover the connection safely is to disconnect from the server (issuing an ""unclean server"" error message).

By default, the ORM issues a commit after every `INSERT` or `UPDATE` made through the ORM. If the very last thing the Django process does before disconnecting is an `INSERT` or an `UPDATE`, it won't run into the pgbouncer ""unclean server"" problem, and the connection won't be idle in transaction (as long as another query isn't run). But not every request makes a modification to the database as its last query, so most of the time you'll run into this issue.

One workaround is to use the `TransactionMiddleware`, but that won't help if you're using the ORM outside of the request framework (e.g., in cron jobs, standalone scripts, or Celery tasks).

I'm not sure what a proper fix would look like (or if this even warrants a fix). I think ideally, Django would by default issue a `ROLLBACK` after every request. It could also issue a `ROLLBACK` when disconnecting from the database. At very the least, this issue should be documented.

Also note that while this is related to #16047, the fix for that issue won't resolve this one."	Bug	closed	Database layer (models, ORM)	1.3	Normal	fixed	psycopg2 transactions	Adrian Holovaty Anssi Kääriäinen Aymeric Augustin tcpip4000 mdupuis iacobcatalin@… gerdemb	Accepted	0	0	0	0	0	0