Clients with aborted transactions taint the pool

[andrewdep]

I have an issue that begins with a bug in our code. It's easy enough for us to fix our code, but I'm not sure if it is also a bug in node-postgres. The issue is if you get a Client from the pool, pauseDrain it, start a transaction, execute a query that results in an error (which puts the transaction into an aborted state), but fail to roll the transaction back before resumeDrain is called, then the Client is returned to the pool in an aborted transaction state. The next user to get that particular Client from the pool will have Postgres errors thrown at them: "current transaction is aborted, commands ignored until end of transaction block"
I know the pooling APIs I've used in other languages clean up connections before returning them to the pool, which would prevent this sort of thing, but I'm not sure what node-postgres' philosophy is on this?
Anyway, here is a commit on a fork I made with a test to reproduce the issue: andrewdep@832e48a
If you think it's right for this project, I can do a pull request for the test.

[andrewdep]

Noticed that the "commit" comment in the test ended up on the wrong line, so fixed it: andrewdep@8bf0229

[brianc]

Yeah, I think clients with errors should probably be purged from the pool. I'm going to talk to felixge about this soon and see if we can come up with some sort of share-able database connection pool we can use and hopefully take on all these edge type cases there.

[andrewdep]

We've worked around it for now by blindly committing any client we are about to call 'resumeDrain' on. It was easier than peppering our code with logic. Committing an aborted transaction has the effect of restoring the Client back to a usable state (and, of course, the original transaction is rolled back). Committing a client that has already been committed does nothing.

[brianc]

I've changed the pool substantially to no longer rely on the drain event at all. No pauseDrain no resumeDrain. Documentation is a work in progress.

https://github.com/brianc/node-postgres/wiki/pg
#274