A SQLAlchemy query sometimes works fine, sometimes returns sqlalchemy.exc.InternalError-CodePudding

I have what seems like a DB issue.

I am using FastAPI and SQLAlchemy.

I have an API endpoint that returns all of the objects in the DB.

main.py

from session import session
from models import *

@app.get("/source/")
async def get_news(request: Request):
    sources = Source.get_sources()
    if sources is None:
        raise HTTPException(status_code=404, detail="No sources") 
    
    return JSONResponse([s.to_dict() for s in sources])


import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine
from sqlalchemy.orm import Session



devprod = os.environ.get('DEVPROD')
if devprod:
    logging.debug('Running '   devprod)
if devprod == 'DEV':
    engine_string = 'url'
elif devprod == 'PROD':
    engine_string = 'sqlite pysqlite:///:memory:'
else:
    engine_string = 'ur'
engine = create_engine(engine_string, echo=True, future=True)
session = Session(engine)

Source.py

def get_sources():
    return session.query(Source).all()

I then have this deployed to heroku and running a PostgreSQL provided by Heroku.

Now I call the endpoint once:

2022-03-18T12:45:08.807470 00:00 heroku[router]: at=info method=GET path="/source/" host=____ request_id=c383505b-3dd5-45c7-86ae-d6b5a0173d70 fwd="91.103.31.14" dyno=web.1 connect=0ms service=3ms status=200 bytes=1338 protocol=https

All good, got a 200 and the right response.

Trying the same request 2 min later:

2022-03-18T12:46:03.613719 00:00 heroku[router]: at=info method=GET path="/source/" host=_____ request_id=eb9baba7-12c1-4087-9828-41e64960853c fwd="91.103.31.14" dyno=web.1 connect=0ms service=3ms status=500 bytes=193 protocol=https

The log states the following error:

2022-03-18T12:46:03.615467 00:00 app[web.1]:     return session.query(Source).all()
2022-03-18T12:46:03.615468 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2759, in all
2022-03-18T12:46:03.615468 00:00 app[web.1]:     return self._iter().all()
2022-03-18T12:46:03.615468 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2894, in _iter
2022-03-18T12:46:03.615468 00:00 app[web.1]:     result = self.session.execute(
2022-03-18T12:46:03.615468 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
2022-03-18T12:46:03.615468 00:00 app[web.1]:     result = conn._execute_20(statement, params or {}, execution_options)
2022-03-18T12:46:03.615469 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
2022-03-18T12:46:03.615469 00:00 app[web.1]:     return meth(self, args_10style, kwargs_10style, execution_options)
2022-03-18T12:46:03.615469 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
2022-03-18T12:46:03.615469 00:00 app[web.1]:     return connection._execute_clauseelement(
2022-03-18T12:46:03.615469 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
2022-03-18T12:46:03.615470 00:00 app[web.1]:     ret = self._execute_context(
2022-03-18T12:46:03.615470 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
2022-03-18T12:46:03.615472 00:00 app[web.1]:     self._handle_dbapi_exception(
2022-03-18T12:46:03.615472 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
2022-03-18T12:46:03.615472 00:00 app[web.1]:     util.raise_(
2022-03-18T12:46:03.615473 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
2022-03-18T12:46:03.615473 00:00 app[web.1]:     raise exception
2022-03-18T12:46:03.615473 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
2022-03-18T12:46:03.615473 00:00 app[web.1]:     self.dialect.do_execute(
2022-03-18T12:46:03.615473 00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
2022-03-18T12:46:03.615474 00:00 app[web.1]:     cursor.execute(statement, parameters)
2022-03-18T12:46:03.615474 00:00 app[web.1]: sqlalchemy.exc.InternalError: (psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block

How is this possible?

CodePudding user response：

I resolved the issue by running in postgresql the command:

SELECT pid , query, * from pg_stat_activity
  WHERE state != 'idle' ORDER BY xact_start;

And then for each transaction id I did this command:

select pg_terminate_backend(2427731);

Then I also run command:

from session import session

session.rollback()

The issue disappeared. Guess I had a stuck transaction that was causing an issue.