How do i reconnect to postgreSQL in psycopg2 threaded connection class? Failure caused by SSL SYSCALL error: EOF detected in Azure?

user_2238704 0
Viewed 784

Our application was working fine till we ported to Microsoft database for PostgreSQL in Azure. Then periodically, our application fails for no real reason and we have SSL SYSCALL errors all over the place - on DELETE's and so on. We have tried everything described in the internet- use keepalive args, RAM, memory and everything else . We want to try automatically reestablishing the connection. But we have a threaded connection pool. have looked at this thread Psycopg2 auto reconnect inside a class But our functions that read the database are in another class. So we have two questions:

1)What is the cause of the SSL SYSCALL errors . I have searched all threads and the usual suspects are ruled out. 2)How do i reconnect on failure inside a threaded connection pool class--> this is being used in a flask app

Here is how our app is structured

class DBClass(object):
    _instance = None
    conn= None
    def __new__(cls):
        if cls._instance is None:
            cls._instance = object.__new__(cls)
            try:
                max_conn = 12
                keepalive_args = { "keepalives": 1, "keepalives_idle": 25, "keepalives_interval": 4,"keepalives_count": 9,
                }
               
                db._instance.pool = psycopg2.pool.ThreadedConnectionPool(3, max_conn, db=,
                                                                               host=, user=,
                                                                               password=,
                                                                               port=, **keepalive_args)

            except Exception as ex:
                db._instance = None
                raise ex

        return cls._instance

    def __enter__(self):
        self.conn= self._instance.pool.getconn()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self._instance.pool.putconn(self.connection)

    def __del__(self):
        self._instance.pool.closeall()

#another python module has a class called clsEmployee. We have dozens of functions using the above-mentioned database class. Something like this.

    with DBClass() as db:
        pg_conn = db.connection
        cur = pg_conn.cursor()
        cur.execute("SELECT * from emp")
        row = cur.fetchone()[0]
psycopg2 python-3.x postgresql python
1 Answers

There are many ways you could handle this.

The solution proposed by Psycopg2 auto reconnect inside a class Will still work if your calls to execute db work is outside of the DBClass. You just need functions that call the database, and you wrap them with a decorator. All the decorator is doing is adding a loop to allow the function to be called multiple times, wrapping the actual function in a try/except and reconnecting on an except. This is actually a pretty standard way of handling this type of problem as it can work for DB, APIs, or anything that could fail. The one thing you may want to do is add an exponential backoff to your retry (Where the sleep call is).

The other option you have is to create your own subclass of cursor that has the same retry logic inside an overridden version of execute. This will accomplish the same thing, It's just a case of what you think is easier to work with.

Since this is being used in a Flask app you could modify the first approach and instead of doing the retry at the model code level, You could do the retry on the Flask route level.

user_11218640
Related