sqlalchemy.pool + psycopg2 timeout issue

Question

I have a web application timeout issue, and I'm suspecting the error is in the database. The query is running too long.

How do I increase the allowed-running-time for my setup?

I am using a DB pool by using sqlalchemy and psycopg2.

My database is a Postgres database.

  import psycopg2
  import sqlalchemy.pool as pool

  def generate_conn_string(db_name):
        db_name = db_name.upper()
        conn_string = "host='{}' port='{}' dbname='{}' user='{}' password='{}' ".format(
            os.environ.get('DB_HOST_' + db_name),
            os.environ.get('DB_PORT_' + db_name),
            os.environ.get('DB_NAME_' + db_name),
            os.environ.get('DB_USER_' + db_name),
            os.environ.get('DB_PASSWORD_' + db_name))
        return conn_string

   def get_conn_():
        db_name = "mydb"
        conn_string = Pooling.generate_conn_string(db_name)
        conn = psycopg2.connect(conn_string)
        return conn

   connection_pool = pool.QueuePool(get_conn, max_overflow=30, pool_size=5)

Answer 1

I am assuming you are asking about statement timeouts in PostgreSQL. The way you are using SQLAlchemy is a bit odd, but a vanilla SQLAlchemy constructor accepts an additional connect_args parameter that can be used as such

engine = create_engine(..., connect_args={"options": "-c statement_timeout=5000"})

Note that the timeout is in milliseconds. In your case, you can use options with straight psycopg2/libpq:

conn = psycopg2.connect(conn_string, , options='-c statement_timeout=5000')

Related question: Configure query/command timeout with sqlalchemy create_engine?