Connection pooling

[derrickbeining]

This might be a dumb question: what's the recommended way to do connection pooling with postgresql-typed? Should I be able to use something like resource-pool? I saw this in the docs for PGConnection

These objects are not thread-safe and must only be used for a single request at a time.

And wasn't sure if that had any consequence for pooling connections.

[dylex]

Yes, you can certainly use resource-pool and we have in production. You just have to make sure any queries are complete before returning it to the pool. This is not usually a problem unless you're using pgLazyQuery or low-level prepared query interface like pgFetch, in which case you just have to get all the results first.

I have sometimes worried about exceptions if something goes wrong, or the server shuts down or something, and providing a way to use createPoolCheckAlive but in practice have never had any issues with this, so haven't bothered.

[derrickbeining]

okay great! and thanks for the clarification

[avanov]

@derrickbeining @dylex do you happen to know if such a pool implementation for postgresql-typed released anywhere as an open-source package?

[dylex]

Using it with resource-pool is almost too simple to need a package:

pool <- createPool (pgConnect ..) pgDisconnect 1 300 8
withResource pool $ \db -> ...
destroyAllResources pool

[avanov]

A proper pool will have to take care of various errors too, so that a connection gets removed from a pool when unrecoverable Client/Session/Server errors occur. That would be a nice pice of logic to reuse universally as a library. For instance, here is what considered unrecoverable in a Python pool library. Haskell implementation could handle it with postgresql-error-codes.

[avanov]

Another thing that would fit a library purpose is various callbacks to observe/track pool statistics for monitoring purposes. They tend to repeat from project to project so that they are good candidates to be put together as a library component.