I have the following class that I am using to pool connections for my application.
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("PGUSER"), password=os.getenv("PGPASSWORD"), host=os.getenv("PGHOST"), port=os.getenv("PGPORT"), database=os.getenv("PGDATABASE"))
# 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
I am executing the code in the following manner inside my code by instantiating and calling the cursor.
def Login(self,username,password):
with self.db.get_cursor() as psCursor:
psCursor.execute("select users.username, users.id, string_agg(coalesce(keys.service, ''), ',') from users left join keys on keys.user_id = users.id where users.username = '" + username + "' and users.password='" + password + "'" + "group by (users.username,users.id)")
rows = self.db.getRows(psCursor)
if len(rows) == 0:
return None
if len(rows) > 0:
return rows[0]
But once i stop using the application for a while and try to execute any sql query. I get the error below
2020-09-01T07:11:05.074414377Z File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/gthread.py", line 284, in handle
2020-09-01T07:11:05.074428178Z keepalive = self.handle_request(req, conn)
2020-09-01T07:11:05.074432778Z File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/gthread.py", 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 "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2463, in __call__
2020-09-01T07:11:05.074446678Z return self.wsgi_app(environ, start_response)
2020-09-01T07:11:05.074450879Z File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2449, in wsgi_app
2020-09-01T07:11:05.074455079Z response = self.handle_exception(e)
2020-09-01T07:11:05.074459279Z File "/usr/local/lib/python3.7/site-packages/flask_cors/extension.py", 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 "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1866, in handle_exception
2020-09-01T07:11:05.074472080Z reraise(exc_type, exc_value, tb)
2020-09-01T07:11:05.074476280Z File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
2020-09-01T07:11:05.074480580Z raise value
2020-09-01T07:11:05.074484580Z File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
2020-09-01T07:11:05.074488780Z response = self.full_dispatch_request()
2020-09-01T07:11:05.074492880Z File "/usr/local/lib/python3.7/site-packages/flask/app.py", 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 "/usr/local/lib/python3.7/site-packages/flask_cors/extension.py", 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 "/usr/local/lib/python3.7/site-packages/flask/app.py", 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 "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
2020-09-01T07:11:05.074524382Z raise value
2020-09-01T07:11:05.074528382Z File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
2020-09-01T07:11:05.074532682Z rv = self.dispatch_request()
2020-09-01T07:11:05.074536682Z File "/usr/local/lib/python3.7/site-packages/flask/app.py", 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 "/whaii/routes/index.py", 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 "/whaii/routes/Whaii_Engine.py", line 148, in Login
2020-09-01T07:11:05.074562384Z psCursor.execute("select users.username, users.id, string_agg(coalesce(keys.service, ''), ',') from users left join keys on keys.user_id = users.id where users.username = '" + username + "' and users.password='" + password + "'" + "group by (users.username,users.id)")
2020-09-01T07:11:05.074567584Z psycopg2.DatabaseError: SSL SYSCALL error: Connection timed out
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 SELECT count(*) FROM pg_stat_activity; and I can see that there are about 26 connections that are active but I am confused as to why the query fails.
