[Python] Can't rollback an async session if error in db.commit()

When an error is thrown during await db.commit() call, I am not able to rollback the session due to MissingGreenlet spawn.

DB

async def get_db(self) -> AsyncIterator[AsyncSession]:
session = self.scoped_session()
try:
yield session
except Exception as e: # <- Instead of HTTPException, this is PendingRollbackError
print("Rolling back the session") # <-- This is executed fine
await session.rollback() # <- This will throw a MissingGreenLetError
raise
finally:
await session.close()



Endpoint

async def do_something(db=Depends(get_db)):
try:
await db.commit() # raises IntegrityError due to UniqueConstraintViolation
except IntegrityError as e:
logger.error(f"Caught error {e}")
raise HTTPException(400, "UniqueConstraintViolation")


What is odd that, IntegrityError is handled in do_something which raises HTTPException. But for some reason, instead of HTTPException, get_db catches PendingRollbackError.

Postgres logs


await db.commit() yields the following Postgres LOGS, which suggests that Postgres has ROLLBACKed:

2024-09-24 12:44:58,391 INFO sqlalchemy.engine.Engine UPDATE users_profiles SET username=$1::VARCHAR WHERE users_profiles.id = $2::VARCHAR

2024-09-24 12:44:58,391 INFO sqlalchemy.engine.Engine [generated in 0.00019s] ('xyz', '123')

2024-09-24 12:44:58,579 INFO sqlalchemy.engine.Engine ROLLBACK


If I manually rollback my session, (or otherwise face PendingRollbackError) I get the MissingGreenlet spawn error:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?


If I don't call .rollback(), then a PendingRollbackError is thrown.

The error log for PendingRollbackError is:

sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "users_username_key"
DETAIL: Key (username)=(xyz) already exists.


And a Server Error 500.

What is going on?

Continue reading...