Why would my pytest tests hang before dropping my SQLAlchemy DB?

Question

Here's my conftest.py (some code deleted for brevity)

from trip_planner import create_app, db as _db
from trip_planner.models import User
from test import TestConfig, test_instance_dir


@pytest.fixture(scope='session', autouse=True)
def app(session_mocker: pytest_mock.MockerFixture):
    static_folder = mkdtemp(prefix='static')
    _app = create_app(TestConfig(), instance_path=test_instance_dir,
                      static_folder=static_folder)
    ctx = _app.app_context()
    ctx.push()
    session_mocker.patch('trip_planner.assets.manifest',
                         new=defaultdict(str))

    yield _app

    ctx.pop()
    os.rmdir(static_folder)


@pytest.fixture(scope='session')
def db(app):
    _db.create_all()
    seed_db(_db)

    yield _db

    _db.drop_all()


def seed_db(db) -> User:
    sessionmaker = db.create_session({'autocommit': False})
    session = sessionmaker()

    user = User(username='username',
                password_digest=bcrypt.hash('password'))
    session.add(user)

    session.commit()
    session.close()
    return user


@pytest.fixture(scope='function')
def db_session(db):
    session = db.create_scoped_session(options=dict(
        autocommit=False, autoflush=False
        ))

    db.session = session
    with session.begin_nested():
        yield session

    session.rollback()
    session.remove()


@pytest.fixture(scope='function')
def app_client(app):
    with app.test_client() as c:
        yield c


@pytest.fixture(scope='function')
def session_user(db_session, app_client) -> int:
    user_id, = db_session.query(User.id).filter_by(username='username').one()
    with app_client.session_transaction() as sess:
        sess['user_id'] = user_id
    return user_id

When my tests pass, pytest hangs. I'm only able to stop it with killall. Inspection of the test database reveals that the relationships were not, in fact, dropped.

How do I remedy this?

user_2733119 · Answer

Apparently, it's a well-known problem with PostgreSQL specifically, here's the discussion.

The way I solved it was adding _db.close_all_sessions() before dropping all tables:

@pytest.fixture(scope='session')
def db(app):
    _db.create_all()
    seed_db(_db)

    yield _db

    _db.close_all_sessions()
    _db.drop_all()

user_2733119 · Answer

Another reason that might have been the case before, not sure. But it's worth checking if you check pg_stat_activity and see that your queries hang on obtaining advisory locks.

Apparently advisory locks can be session-level or transaction-level in PostgreSQL. A session-level advisory lock is not released upon the end of transaction, only on disconnect. It can cause overlapping sessions to hang while one is trying to roll everything back, and the other trying to take an advisory lock.

A session-level advisory lock is obtained via pg_advisory_lock functions and a transaction-level advisory lock is obtained via pg_advisory_xact_lock functions.

Why would my pytest tests hang before dropping my SQLAlchemy DB?

2 Answers