Improve connection reset under PostgreSQL

[Aymeric Augustin]

On django-developers, Christophe Pettus says:

It would be helpful to have a backend method that performers the "restore connection between uses" function, rather than just use connection.abort() (of course, the default implementation can use that). For example, on PostgreSQL, ABORT; DISCARD ALL is the recommended way of resetting a connection, so being able to implement that would be great.

This was independently suggested by Anssi, and it looks like a good idea to implement.

[Carl Meyer]

I think "ABORT; DISCARD ALL" may also need to be followed up with self.init_connection_state(), because it would otherwise discard the session state that Django sets up for each connection.

[Aymeric Augustin]

Yes, it should.

[Asif Saifuddin Auvi]

any update on this?

[Aryan Gholizadeh Mojaveri]

Hi all, sorry if im being stupid but is this ticket suggesting a new backend method that does not replace the default close behavior? In that case when would this be used? Given that this ticket is very old, can i start working on a PR with the assumption that it does imply replacement?

[ej]

Hi there, I managed to reproduce this here: ​gist. Could someone kindly look at this and tell me if I'm in the right direction? Thanks!

Here's what I found to be the root cause: with persistent connections (CONN_MAX_AGE > 0), ​close_old_connections() --> ​close_if_unusable_or_obsolete() only checks autocommit, errors, and connection age. If the connection is kept, nothing resets it, so session-level state (SET, temp tables, advisory locks, PREPARE, LISTEN) carries over to the next request on the same thread.

Here are some scenarios I ran in the gist (request 1 sets session state, request 2 on the same thread checks for it):

Scenario	Same PG session reused?	State leaks?	Time zone
1. CONN_MAX_AGE=0	no	no	OK
2. CONN_MAX_AGE=None (current behavior)	yes	yes	OK
3. + ABORT; DISCARD ALL	yes	no	wrong (falls back to server default)
4. + ABORT; DISCARD ALL + init_connection_state()	yes	no	OK

Scenario 3 confirms comment 2: DISCARD ALL also wipes the time zone / role that ​_configure_connection() sets, so init_connection_state() needs to run again.

IMHO it would be nice to provide the reset as an opt-in safety net (thinking about Scenario 2). The ​docs tell the developer that they should "either restore Django's defaults at the end of each request, force an appropriate value at the beginning of each request, or disable persistent connections.", so that's why I think it's a "nice to have".

Comment 5 asks if this should replace or add to the close behavior. I would lean towards this being additive and explicit so we only reset when the connection is kept and the developer asks for the reset. Does that work?

That being said, the cost is that Scenario 4 adds 3 round trips per request. Could it be cut to 1 by batching the documented equivalent of DISCARD ALL with SET TIME ZONE? but since it would be opt-in, the cost should be fine (i.e. the developer would know about the cost)?

Lastly, where would be the right place to add this?

1. PostgreSQL-only option à la assume_role (e.g. OPTIONS["reset_on_reuse"]?) that runs the reset when ​close_if_unusable_or_obsolete() keeps the connection (once per request)?
2. Extend the persistent connections caveats docs with SET LOCAL and a request_finished receiver example?
3. Another place ?