Breaking change in 0.42.0 - multiple database sessions

[jg197]

Hi,

After upgrading from 0.41.2 to 0.42.0, the following errors have been introduced when performing uv run --python 3.12 pytest -v

    | sqlalchemy.exc.OperationalError: (psycopg.errors.ObjectInUse) database "test" is being accessed by other users
    | DETAIL:  There are 5 other sessions using the database.
    | [SQL: DROP DATABASE "test"]
    | (Background on this error at: https://sqlalche.me/e/20/e3q8)

I think the removal of this block has introduced the issue. If this is an intention for 0.42.0 then this version should be flagged as a breaking-change?

Thanks.

[dolfinus]

Same issue after upgrade. I was connecting to freshy created database with engine without calling engine.dispose() afterwards, and this lead to drop_database(url) raising the same error.

[Samk13]

Also hit this after upgrading to 0.42.0. Our test suite (Invenio modules) started failing with:

IntegrityError: duplicate key value violates unique constraint

OperationalError: database "... " is being accessed by other users

Rolling back to 0.41.2 makes the errors disappear. Looks like 0.42.0 leaves extra sessions open during drop_database(), which breaks teardown in pytest. Pinning <0.42 is a temporary workaround, but this should probably be marked as a breaking change.

[dolfinus]

Looks like 0.42.0 leaves extra sessions open during drop_database()

This should not be the case, this function calls engine.dispose() after database was dropped:
https://github.com/kvesteri/sqlalchemy-utils/blob/0.42.0/sqlalchemy_utils/functions/database.py#L642

Probably something in test or another fixture didn't close connection before database is dropped.

[phistep]

Definitely a breaking change. Most of our database tests fail now with the multiple connections error. Even if it is not a bug, it breaks cases that used to work before.

[reubinoff-prime]

maybe it make sense to add new method that drop connections or give the user option, with flag, to remove it

the old method:

elif dialect_name == 'postgresql':
        with engine.begin() as conn:
            # Disconnect all users from the database we are dropping.
            version = conn.dialect.server_version_info
            pid_column = (
                'pid' if (version >= (9, 2)) else 'procpid'
            )
            text = '''
            SELECT pg_terminate_backend(pg_stat_activity.{pid_column})
            FROM pg_stat_activity
            WHERE pg_stat_activity.datname = '{database}'
            AND {pid_column} <> pg_backend_pid();
            '''.format(pid_column=pid_column, database=database)
            conn.execute(sa.text(text))

            # Drop the database.
            text = f'DROP DATABASE {quote(conn, database)}'
            conn.execute(sa.text(text))

Relevant PR

[franciscovelez]

Same here, restoring the old "disconnect users" code made it work again