Attempting to drop either database fails with OperationalError: "Database ... is being accessed by other users", after using comparer constructed from URLs #32
Description
Attempting to drop either database fails with OperationalError: "Database ... is being accessed by other users", after using comparer constructed from URLs.
> cursor.execute(statement, parameters)
E sqlalchemy.exc.OperationalError: (psycopg2.errors.ObjectInUse) database "models_93ce1928d78811f0ae3006a21d525972" is being accessed by other users
E DETAIL: There are 2 other sessions using the database.
E
E [SQL: DROP DATABASE models_93ce1928d78811f0ae3006a21d525972]
E (Background on this error at: https://sqlalche.me/e/14/e3q8)
It looks like the comparer is failing to close the database connections.
SQLAlchemy==1.4.54
sqlalchemy-diff==1.1.0
SQLAlchemy-Utils==0.42.0
The relevant portion of my test code:
comparer = sqlalchemydiff.comparer.Comparer.from_params(
models_db_url,
migration_db_url
)
result: sqlalchemydiff.comparer.CompareResult = comparer.compare(
one_alias='models-based', two_alias='migration-based')
import sqlalchemy.util.queue
print(f'ZZZ {type(comparer.db_one_engine.pool._pool)=}')
while True:
try:
conn: engine.Connection = comparer.db_one_engine.pool._pool.get(False)
print(f'ZZZ comparer.db_one_engine {conn.driver_connection.status=}')
print(f'ZZZ comparer.db_one_engine {conn.driver_connection.closed=}')
except sqlalchemy.util.queue.Empty:
break
while True:
try:
conn: engine.Connection = comparer.db_two_engine.pool._pool.get(False)
print(f'ZZZ comparer.db_two_engine {conn.driver_connection.status=}')
print(f'ZZZ comparer.db_two_engine {conn.driver_connection.closed=}')
except sqlalchemy.util.queue.Empty:
break
drop_database(models_db_url)
drop_database(migration_db_url)
The resulting output shows that the db connections in both db engines created by sqlalchemydiff are still open - driver_connection.closed=0, which causes the said OperationalError:
comparer.db_one_engine.pool.status()='Pool size: 5 Connections in pool: 2 Current Overflow: -3 Current Checked out connections: 0'
comparer.db_two_engine.pool.status()='Pool size: 5 Connections in pool: 2 Current Overflow: -3 Current Checked out connections: 0'
ZZZ type(comparer.db_one_engine.pool._pool)=<class 'sqlalchemy.util.queue.Queue'>
ZZZ comparer.db_one_engine conn.driver_connection.status=1
ZZZ comparer.db_one_engine conn.driver_connection.closed=0
ZZZ comparer.db_one_engine conn.driver_connection.status=1
ZZZ comparer.db_one_engine conn.driver_connection.closed=0
ZZZ comparer.db_two_engine conn.driver_connection.status=1
ZZZ comparer.db_two_engine conn.driver_connection.closed=0
ZZZ comparer.db_two_engine conn.driver_connection.status=1
ZZZ comparer.db_two_engine conn.driver_connection.closed=0
Traceback snippet
/usr/local/lib/python3.11/site-packages/sqlalchemy_utils/functions/database.py:640: in drop_database
conn.execute(sa.text(text))
/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1385: in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334: in _execute_on_connection
return connection._execute_clauseelement(
/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1577: in _execute_clauseelement
ret = self._execute_context(
/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1953: in _execute_context
self._handle_dbapi_exception(
/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2134: in _handle_dbapi_exception
util.raise_(
/usr/local/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211: in raise_
raise exception
/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1910: in _execute_context
self.dialect.do_execute(
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0xffff9c1a4690>, cursor = <cursor object at 0xffff9c693f10; closed: -1>
statement = 'DROP DATABASE models_93ce1928d78811f0ae3006a21d525972', parameters = {}
context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0xffff9c1fbc50>
def do_execute(self, cursor, statement, parameters, context=None):
> cursor.execute(statement, parameters)
E sqlalchemy.exc.OperationalError: (psycopg2.errors.ObjectInUse) database "models_93ce1928d78811f0ae3006a21d525972" is being accessed by other users
E DETAIL: There are 2 other sessions using the database.
E
E [SQL: DROP DATABASE models_93ce1928d78811f0ae3006a21d525972]
E (Background on this error at: https://sqlalche.me/e/14/e3q8)
/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736: OperationalError