[BUG] Making it a Postgres database URI causes SQLAlchemy too many Connections, Dash App Does not work

[mdylan2]

If I make the DATABASE_URI a Postgres database, launch the app server locally, and then navigate to the dashboard mounted at /dashboard, I get the following error and the dashboard does not launch:

psycopg2.OperationalError: FATAL:  too many connections for role "ulyvpgfa"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask/app.py", line 2088, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.handle_exception(e)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask/app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask/app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask/app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask/app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask_login/utils.py", line 270, in decorated_view
    elif not current_user.is_authenticated:
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/werkzeug/local.py", line 422, in __get__
    obj = instance._get_current_object()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/werkzeug/local.py", line 544, in _get_current_object
    return self.__local()  # type: ignore
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask_login/utils.py", line 26, in <lambda>
    current_user = LocalProxy(lambda: _get_user())
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask_login/utils.py", line 346, in _get_user
    current_app.login_manager._load_user()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/flask_login/login_manager.py", line 318, in _load_user
    user = self._user_callback(user_id)
  File "/home/ubuntu/dash_on_flask/app/models.py", line 11, in load_user
    return User.query.get(int(id))
  File "<string>", line 2, in get
    
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 390, in warned
    return fn(*args, **kwargs)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 943, in get
    return self._get_impl(ident, loading.load_on_pk_identity)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 947, in _get_impl
    return self.session._get_impl(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2839, in _get_impl
    return db_load_fn(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 524, in load_on_pk_identity
    session.execute(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1688, in execute
    conn = self._connection_for_bind(bind)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1529, in _connection_for_bind
    return self._transaction._connection_for_bind(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3166, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3245, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3215, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2069, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 307, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 767, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 425, in checkout
    rec = pool._do_get()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 253, in _create_connection
    return _ConnectionRecord(self)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 368, in __init__
    self.__connect()
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 611, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    connection = pool._invoke_creator(self)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/ubuntu/phenomic_scrna_embed/dashboard/venv/lib/python3.8/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  too many connections for role "ulyvpgfa"

(Background on this error at: https://sqlalche.me/e/14/e3q8)

[okomarov]

Do you have a repo where you can reproduce the issue? I can't help you based on this alone.

[mdylan2]

Here you go: https://github.com/mdylan2/dash_on_flask. I've also included a dummy database connection in the export commands. Let me know if you have any issues.

Run the flask app and open it in a browser, create an account, sign in, navigate to the dashboard, refresh a bit and keep an eye on the terminal

What's weird is that is happens only sometimes, not all the time...

Looking at my database, there are a bunch of idle ROLLBACKS

[mdylan2]

After some debugging (adding a print statement inside the @login.user_loader decorator function, it seems like when you navigate to the dash app, the @login.user_loader function gets called many times. This is probably the issue, but I have no idea how to fix it.

Edit: Alright, this makes sense, each view function in the dash app has a login_required decorator. So, every time the dash app is opened, each Dash view route is invoked, Flask_login opens up a connection to the database, querying for the current user, getting their credentials. I think this is opening up a bunch of connections to my database. The database is a free version I got on Elephant SQL so I only have maximum of 5 connections allowed so overloads are easy. Does this make sense?

[mdylan2]

This also relates to the Stack Overflow post here