Sqlalchemy: [Bug...maybe?] Postgres: sqlalchemy hangs when deleting table after fetching records from it

[leberknecht]

Ahoi!

Im not quite sure if this is actually something that needs to be fixed or not, i stumbled over it by mistake. But it took me a bit to find the problem, so maybe its worth writing it down. So on our CI we have a preparation step like "drop all tables from the DB, then re-create the schema" (as we have different fixture-sets for behave and pytest). This worked well for a while with sqlite, but when we switched the CI to run against postgres, the problem occurred.
Steps to reproduce:
a) launch a postgres:

docker run -e POSTGRES_PASSWORD=postgres -p 5431:5432 postgres

b) script:

from sqlalchemy import Column, Integer
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

db_engine = create_engine('postgres://postgres:postgres@localhost:5431/postgres')
Session = sessionmaker(bind=db_engine)
db_session = Session()

Base = declarative_base()

class MyEntity(Base):
    __tablename__ = 'my_entity'
    id = Column(Integer, primary_key=True)


Base.metadata.create_all(db_engine)

db_session.add(MyEntity())
db_session.commit()

[print(x.id) for x in db_session.query(MyEntity).all()]
for table in reversed(Base.metadata.sorted_tables):
    print("dropping table {}".format(table))
    db_engine.execute('DROP TABLE IF EXISTS "{}"'.format(table))
print("done")

Output:

1
dropping table my_entity

And then hangs there, no exit.

Note: if we comment out the line [print(x.id) for x in db_session.query(MyEntity).all()], the script finishes without problems. Also runs fine with sqlite.

If this is a wont-fix for you, im fine with it..i guess there are not many legit use-cases for this kind of stuff :)

[zzzeek]

it's pretty much the oldest PostgreSQL gotcha there is, it's very aggressive about locking when concurrent transactions are open. Your usage of session.query(..).all() constitutes starting a transaction. I'd advise calling .close() on all Session objects in test suites, or at the very least ending them with .commit() or .rolback(). You will see this throughout SQLAlchemy's test suite where tests that don't clean up their transactions are a constant source of issues for the DROP.

[leberknecht]

Aye! Sounds like there is nothing to fix then, shall we close it?

[zzzeek]

pretty much yeah :)

[crogersdev]

had the same problem in very much the same circumstances; closing out integration test database tables. making sure the current session has committed before executing the drop table statement fixed it for me, as well. thank you for the sanity check @leberknecht and thank you for the (new to me) information about the oldest postgresql gotcha there is, @zzzeek