Released pool connections should reset state before being reused

[atombender]

The usual way used by pgpool and pgbouncer is to call DISCARD ALL on the connection when it is returned to the pool. ORMs such as ActiveRecord also do this. This is to ensure that cursors, temporary tables etc. are not inherited by the next connection.

I see that releasing calls ROLLBACK and that listens are also closed, but I don't see any other cleanup. It's worth noting that DISCARD ALL includes an internal call equivalent to UNLISTEN *.

[jackc]

I think there is a bit of a difference between the typical uses Rails ActiveRecord and pgbouncer and the pgx conn pool. AFAIK, ActiveRecord allocates connections on a thread basis, so there are not frequent acquire and release cycles, but in pgx every query called through the pool is a separate acquire/release cycle. pgbouncer is designed for multiple simultaneous applications that logically have different types of connections. but in pgx all connections are logically the same.

But I wanted to see if it could be done so I created a proof-of-concept in branch conn-pool-reset. But there is a problem that preclude its inclusion as it is now.

First, we can't use DISCARD ALL because we definitely don't want to lose any prepared statements, and we probably don't want to lose manually assigned session variables. That's not a huge problem because we can just call all the individual cleanup statements manually in a single Exec.

The bigger problem is performance. Running tests with https://github.com/jackc/go_db_bench found that light-weight queries took 3x longer. 60,000ns vs 20,000ns. This was running against localhost. Obviously, the relative overhead would be reduced when doing more work than a single query selecting a single value, but it's quite significant overhead for simple queries.

[jackc]

The v4 connection pool now supports an after release hook.

[atombender]

Excellent, thanks!

[mvrhov]

@jackc now we need something that gets run after the connection is taken from the pool. e.g. something to make #288 easier. (setting env variable and/or timezone etc., setting default search path.) each time the connection is taken from the pool

[jackc]

@mvrhov I'm not sure if there's a good way for pgx to provide that functionality. If the pool had a AfterCheckout hook, how would arguments be passed through all the methods that implicitly checkout, use and release a connection (such as Query|QueryRow|Exec). And without passing arguments in per checkout, there is nothing that can't be done with a combination of AfterConnect and AfterRelease.

Assuming this desired functionality is in the context of a web application, I would probably consider using middleware to acquire a connection, set it up, and make it available for downstream HTTP handlers and release the connection when they return.