title: Sqlalchemy session.rollback from IntegrityError causes queuepool to run out of handlers?
body: <p>I have the following table:</p>
  
  <pre><code>class Feedback(Base):
    __tablename__ = 'feedbacks'
    __table_args__ = (UniqueConstraint('user_id', 'look_id'),)
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    look_id = Column(Integer, ForeignKey('looks.id'), nullable=False)
  </code></pre>
  
  <p>I am currently inserting a lot of entries into this table that will be violating that UniqueConstraint. </p>
  
  <p>I am using the following code:</p>
  
  <pre><code>  for comment in session.query(Comment).filter(Comment.type == Comment.TYPE_LOOK).yield_per(100):
      feedback = Feedback()
      feedback.user_id = User.get_or_create(comment.model_id).id
      feedback.look_id = comment.commentable_id
      session.add(feedback)
      try:        # Refer to T20
        session.flush()
      except IntegrityError,e:
        print "IntegrityError", e
        session.rollback()
    session.commit()
  </code></pre>
  
  <p>and I am getting the following error:</p>
  
  <pre><code>IntegrityError (IntegrityError) duplicate key value violates unique constraint "feedbacks_user_id_look_id_key"
  DETAIL:  Key (user_id, look_id)=(140, 263008) already exists.
   'INSERT INTO feedbacks (user_id, look_id, score) VALUES (%(user_id)s, %(look_id)s, %(score)s) RETURNING feedbacks.id' {'user_id': 140, 'score': 1, 'look_id': 263008}
  IntegrityError (IntegrityError) duplicate key value violates unique constraint "feedbacks_user_id_look_id_key"
  ...
  (there's about 24 of these integrity errors here)
  ...
  DETAIL:  Key (user_id, look_id)=(173, 263008) already exists.
   'INSERT INTO feedbacks (user_id, look_id, score) VALUES (%(user_id)s, %(look_id)s, %(score)s) RETURNING feedbacks.id' {'user_id': 173, 'score': 1, 'look_id': 263008}
  No handlers could be found for logger "sqlalchemy.pool.QueuePool"
  Traceback (most recent call last):
    File "load.py", line 40, in &lt;module&gt;
      load_crawl_data_into_feedback()
    File "load.py", line 21, in load_crawl_data_into_feedback
      for comment in session.query(Comment).filter(Comment.type == Comment.TYPE_LOOK).yield_per(100):
    File "/Volumes/Data2/Dropbox/projects/Giordano/venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2337, in instances
      fetch = cursor.fetchmany(self._yield_per)
    File "/Volumes/Data2/Dropbox/projects/Giordano/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 3230, in fetchmany
      self.cursor, self.context)
    File "/Volumes/Data2/Dropbox/projects/Giordano/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 3223, in fetchmany
      l = self.process_rows(self._fetchmany_impl(size))
    File "/Volumes/Data2/Dropbox/projects/Giordano/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 3343, in _fetchmany_impl
      row = self._fetchone_impl()
    File "/Volumes/Data2/Dropbox/projects/Giordano/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 3333, in _fetchone_impl
      self.__buffer_rows()
    File "/Volumes/Data2/Dropbox/projects/Giordano/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 3326, in __buffer_rows
      self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
  sqlalchemy.exc.ProgrammingError: (ProgrammingError) named cursor isn't valid anymore None None
  </code></pre>
  
  <p>Before you jump to conclusions about this error being caused by yield_per, I can assure you that yield_per is not the culprit here. </p>
  
  <p>I tried the same code without the unique constraints and I did not experience any error at all.</p>
  
  <p>I believe that the integrity errors are causing <strong>No handlers could be found for logger "sqlalchemy.pool.QueuePool"</strong>. </p>
  
  <p>I assume that every integrity error is killing each "thread" in the queuepool. </p>
  
  <p>Can someone enlighten me as to what is happening? </p>
  
  <p>If I can't do much about the data at this point, what would you recommend me to do?  </p>