Intermittent failures in destroy_database()

[davidparsson]

We're using alembic-verify and sqlalchemy-diff to verify the correctness of our migrations on a PostgreSQL database, but every now and then the destroy_database(temporary_uri) call in the test's teardown fails with the following error:

(psycopg2.ProgrammingError) must be a member of the role whose process is being terminated

The database is created in the test's setup so nothing outside of the test should know that it exists, and it's only used for a single test. Do you have any ideas on what could be wrong?

Full stacktrace:

File "/usr/lib/python3.5/unittest/case.py", line 58, in testPartExecutor
    yield
  File "/usr/lib/python3.5/unittest/case.py", line 603, in run
    self.tearDown()
  File ".venv/lib/python3.5/site-packages/nose/case.py", line 385, in tearDown
    try_run(self.inst, ('teardown', 'tearDown'))
  File ".venv/lib/python3.5/site-packages/nose/util.py", line 471, in try_run
    return func()
  File "migration/tests/test_upgrade_downgrade.py", line 28, in teardown
    destroy_database(self.database_uri)
  File ".venv/lib/python3.5/site-packages/sqlalchemydiff/util.py", line 83, in destroy_database
    drop_database(uri)
  File ".venv/lib/python3.5/site-packages/sqlalchemy_utils/functions/database.py", line 603, in drop_database
    connection.execute(text)
  File ".venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 906, in execute
    return self._execute_text(object, multiparams, params)
  File ".venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
    statement, parameters
  File ".venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File ".venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File ".venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File ".venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 182, in reraise
    raise value.with_traceback(tb)
  File ".venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File ".venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
(psycopg2.ProgrammingError) must be a member of the role whose process is being terminated
 [SQL: "\n        SELECT pg_terminate_backend(pg_stat_activity.pid)\n        FROM pg_stat_activity\n        WHERE pg_stat_activity.datname = 'temp_29aa5e9b372342c2a1d0de1bf24fedfb'\n          AND pid <> pg_backend_pid();\n        "]

[davidparsson]

I've since found that PostgreSQL seem to be regularly running processes on all databases, as some kind of a superuser. If trying to remove the database with another user while this is happening, the removal will fail.

I managed to work around this issue by catching the ProgrammingError and trying again, after a little while.

[davidparsson]

I would still consider this to be an issue.