peewee connection pooling returns broken connections from the pool

If you execute some bad SQL like select * from not_a_table; postgres will leave you in a CONNECTION_BAD state. Requiring you to rollback or reset the connection before continuing. Peewee intentionally leaves this up to the user to handle (either through the autorollback param or otherwise). If the user doesn't handle it, all future SQL statements will fail.

This is fine, but if the user doesn't handle it, the connection should not be put back into the pool as-is. Connection pools should return functionally new connections each time. The next user of the pool should not have to worry about what the previous user did or did not do.

Example of the pool returning a borked connection:

import playhouse.db_url
DB_URL = 'postgresext+pool:///template1'
db = playhouse.db_url.connect(DB_URL)

db.connect()
try:
  db.execute_sql('select * from not_a_table;')
except Exception as e:
  print 'got', repr(e.message), 'but ignoring'

# close the bad connection, returning it to the pool
db.close()

# get a "new" connection from the pool
db.connect()
# this should not fail, but does
db.execute_sql('select 1;')
db.close()

psycopg2 knows in-memory (not requiring a network call) the state of the connection, so it should be easy to detect without a performance hit, and offers a reset() method that fixes it, so this should be an easy fix.

keredson / peewee

peewee connection pooling returns broken connections from the pool #11