Proper handling of SQLAlchemy database sessions

[costrouc]

I am convinced this is due to sqlalchemy sessions not being properly handled within conda-store server and workers. This also addresses issues seen in #459. There should be clear boundaries around sessions and they should never be passed between threads since they are not thread-safe https://docs.sqlalchemy.org/en/13/orm/session_basics.html#is-the-session-thread-safe.

Currently main is broken. This was shown clearly by

conda-store/conda-store-server/conda_store_server/server/app.py

Line 190 in 5507732

parent=self, log=self.log, authentication_db=self.conda_store.db

which essentially passes a sqlalchemy session to the Traitlets object conda_store_server.server.auth.Authentication and the value is used across threads (each fastapi route call potentially runs on a separate thread).

A simple fix is to pass the session_factory instead of session:

• 74d37e2#diff-8aca98d789e72a0f566677c4fd621c36710f5f7962296ae7ba9775a69bb3bebfR246
• 74d37e2#diff-6e917bb6802f2371734d67dc682bca330426e10a04625ef77f396d208f943a3eR190

But this "fix" leads to additional errors about closing sessions in other places. See the following errors message.

emy/orm/attributes.py", line 942, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/home/costrouc/.conda/envs/conda-store-server-dev/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 978, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/home/costrouc/.conda/envs/conda-store-server-dev/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 863, in _load_for_state
    raise orm_exc.DetachedInstanceError(
sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Build at 0x7ff1c197e0e0> is not bound to a Session; lazy load operation of attribute 'specification' cannot proceed (Background on this error at: https://sqlalche.me/e/14/bhk3)

All this is to say that we need to have a clearer boundaries on when a session begins/ends.

• For the web server it should start/end with each web requests since a single web request run in the same thread see 74d37e2#diff-eb87ec4963d68bcf72a2229159a5369d892597d1c118ea43f37357e78bf19172R25 and 74d37e2 on how this is done
• For the celery tasks we need to start/end each session for a task

The major refactor here is that conda_store_server.app.CondaStore cannot handle the session instance and instead it needs to be passed along from the web request or celery task.

[costrouc]

Figured out a sneaky bug in conda-store having to do with threads.

from fastapi import Depends

def f():
    return [1, 2, 3]

async def g():
    return [1, 2, 3]

@app.get('/')
def list_items(items: Depends(f))
    return items

The function f is not guaranteed to run in the same thread as list_items but g is. I think this also explains some of the subtle issues with celery being setup on the server.