title: Python psycopg2 pool connection timeout
body: <p>I have the following class that I am using to pool connections for my application.</p>
  <pre><code>import os
  import psycopg2
  import contextlib
  from psycopg2 import pool
  from contextlib import contextmanager
  
  class DbHandler:    
      def __init__(self):
          self.reconnect_pool()
  
  
      def reconnect_pool(self):
          self.pgPool = psycopg2.pool.SimpleConnectionPool(1, 128, user=os.getenv(&quot;PGUSER&quot;), password=os.getenv(&quot;PGPASSWORD&quot;), host=os.getenv(&quot;PGHOST&quot;), port=os.getenv(&quot;PGPORT&quot;), database=os.getenv(&quot;PGDATABASE&quot;))
          
      # Get Cursor
      @contextmanager
      def get_cursor(self):
          if(self.pgPool):
              conn = self.pgPool.getconn()
              if(conn):
                  try:
                      yield conn.cursor()
                      conn.commit()
                  finally:
                      self.pgPool.putconn(conn)
          else:
              self.reconnect_pool() 
  
  
       # Get Cursor
      @contextmanager
      def get_connection(self):
          if(self.pgPool):
              conn = self.pgPool.getconn()
              if(conn):
                  try:
                      yield conn
                  finally:
                      self.pgPool.putconn(conn)
          else:
              self.reconnect_pool() 
  
  
      # Helper function
      def getRows(self, cursor):
          rows=[]
          row = cursor.fetchone()
          while row is not None:
              rows.append(row)
              row = cursor.fetchone()
          return rows
  
  
      def getPoolSize(self):
          if(self.pgPool):
              return len(self.pgPool._used)
          else:
              return -1
  
  </code></pre>
  <p>I am executing the code in the following manner inside my code by instantiating and calling the cursor.</p>
  <pre><code> def Login(self,username,password):
          with self.db.get_cursor() as psCursor:
              psCursor.execute(&quot;select users.username, users.id, string_agg(coalesce(keys.service, ''), ',') from users left join keys on keys.user_id = users.id where users.username = '&quot; + username + &quot;' and users.password='&quot; + password + &quot;'&quot; + &quot;group by (users.username,users.id)&quot;)
              rows = self.db.getRows(psCursor)                    
              if len(rows) == 0:
                  return None
              if len(rows) &gt; 0:
                  return rows[0]
  </code></pre>
  <p>But once i stop using the application for a while and try to execute any sql query. I get the error below</p>
  <pre><code>2020-09-01T07:11:05.074414377Z   File &quot;/usr/local/lib/python3.7/site-packages/gunicorn/workers/gthread.py&quot;, line 284, in handle
  2020-09-01T07:11:05.074428178Z     keepalive = self.handle_request(req, conn)
  2020-09-01T07:11:05.074432778Z   File &quot;/usr/local/lib/python3.7/site-packages/gunicorn/workers/gthread.py&quot;, line 333, in handle_request
  2020-09-01T07:11:05.074438078Z     respiter = self.wsgi(environ, resp.start_response)
  2020-09-01T07:11:05.074442378Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 2463, in __call__
  2020-09-01T07:11:05.074446678Z     return self.wsgi_app(environ, start_response)
  2020-09-01T07:11:05.074450879Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 2449, in wsgi_app
  2020-09-01T07:11:05.074455079Z     response = self.handle_exception(e)
  2020-09-01T07:11:05.074459279Z   File &quot;/usr/local/lib/python3.7/site-packages/flask_cors/extension.py&quot;, line 161, in wrapped_function
  2020-09-01T07:11:05.074463479Z     return cors_after_request(app.make_response(f(*args, **kwargs)))
  2020-09-01T07:11:05.074467779Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 1866, in handle_exception
  2020-09-01T07:11:05.074472080Z     reraise(exc_type, exc_value, tb)
  2020-09-01T07:11:05.074476280Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/_compat.py&quot;, line 39, in reraise
  2020-09-01T07:11:05.074480580Z     raise value
  2020-09-01T07:11:05.074484580Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 2446, in wsgi_app
  2020-09-01T07:11:05.074488780Z     response = self.full_dispatch_request()
  2020-09-01T07:11:05.074492880Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 1951, in full_dispatch_request
  2020-09-01T07:11:05.074497181Z     rv = self.handle_user_exception(e)
  2020-09-01T07:11:05.074501181Z   File &quot;/usr/local/lib/python3.7/site-packages/flask_cors/extension.py&quot;, line 161, in wrapped_function
  2020-09-01T07:11:05.074505381Z     return cors_after_request(app.make_response(f(*args, **kwargs)))
  2020-09-01T07:11:05.074509581Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 1820, in handle_user_exception
  2020-09-01T07:11:05.074513781Z     reraise(exc_type, exc_value, tb)
  2020-09-01T07:11:05.074520082Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/_compat.py&quot;, line 39, in reraise
  2020-09-01T07:11:05.074524382Z     raise value
  2020-09-01T07:11:05.074528382Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 1949, in full_dispatch_request
  2020-09-01T07:11:05.074532682Z     rv = self.dispatch_request()
  2020-09-01T07:11:05.074536682Z   File &quot;/usr/local/lib/python3.7/site-packages/flask/app.py&quot;, line 1935, in dispatch_request
  2020-09-01T07:11:05.074541083Z     return self.view_functions[rule.endpoint](**req.view_args)
  2020-09-01T07:11:05.074545183Z   File &quot;/whaii/routes/index.py&quot;, line 126, in login
  2020-09-01T07:11:05.074549383Z     user = MatchFunc.Login(str(data.get('username')),str(data.get('password')))
  2020-09-01T07:11:05.074556783Z   File &quot;/whaii/routes/Whaii_Engine.py&quot;, line 148, in Login
  2020-09-01T07:11:05.074562384Z     psCursor.execute(&quot;select users.username, users.id, string_agg(coalesce(keys.service, ''), ',') from users left join keys on keys.user_id = users.id where users.username = '&quot; + username + &quot;' and users.password='&quot; + password + &quot;'&quot; + &quot;group by (users.username,users.id)&quot;)
  2020-09-01T07:11:05.074567584Z psycopg2.DatabaseError: SSL SYSCALL error: Connection timed out
  </code></pre>
  <p>I am guessing it is the way I am calling the cursor. It would be great if someone could point out how I can reuse the connection pool.
  Also, I ran <code>SELECT count(*) FROM pg_stat_activity;</code> and I can see that there are about 26 connections that are active but I am confused as to why the query fails.</p>