Postgres: DISCARD ALL

[jakajancar]

Would be nice if bb8-postgres supported calling DISCARD ALL after the connection is returned to the pool, so it's guaranteed to be "pristine" on the next checkout.

Unless I'm missing something, there isn't even a "post check-in" hook in bb8 right now, only test_on_check_out. I suppose that could be used, but the best is to do it early, release the resources (e.g. UNLISTEN) and reduce the latency on checkout (assuming test_on_checkout is false).

[djc]

Might be nice to have a look at other pooling solutions (r2d2, deadpool, sqlx) and see if/how they support this.

(Note that previously we recommended that LISTEN queries should probably be issued on non-pooled connections, through Pool::dedicated_connection().)

[jakajancar]

PgBouncer has it configurable (server_reset_query) but it defaults to DISCARD ALL. PgBouncer is probably the gold standard when it comes to Postgres pooling.

It’s really not about the LISTEN, but all (currently 9) resources this releases. Another nice thing is that it fails if the connection is inside a transaction, so it helps automatically check that you’re closing them correctly.

I think it needs to be configurable, otherwise you loose the ability to share use prepared statements etc. Choose your own isolation level...

[djc]

I was referring mostly to Rust crates.

I'm unlikely to have time for this any time soon unless someone is willing to pay for it. Will review hooks for this in the connection manager trait, though.

[jakajancar]

Yes, I think hooks are key rather than supporting every single possible reset query. Then one can subclass the postgres manager and do what they want.

[djc]

89 added a way to customize connections at the start of the lifetime rather than the end, which should help with this.