DatabaseError current transaction is aborted commands ignored until end of transaction block

Question

I got a lot of errors with the message     DatabaseError  current transaction is aborted  commands ignored until end of transaction block    after changed from python-psycopg to python-psycopg2 as Django project s database engine   The code remains the same  just don t know where those errors are from

User · Answer

you could disable transaction via  set isolation level 0

User · Answer

If you get this while in interactive shell and need a quick fix  do this   from django db import connection connection  rollback     originally seen in this answer

User · Answer

I am using the python package psycopg2 and I got this error while querying  I kept running just the query and then the execute function  but when I reran the connection  shown below   it resolved the issue  So rerun what is above your script i e the connection  because as someone said above  I think it lost the connection or was out of sync or something  connection   psycopg2 connect user    quot    quot           password    quot    quot           host    quot    quot           port    quot    quot           database    quot    quot   cursor   connection cursor

User · Answer

I encountered a similar behavior while running a malfunctioned transaction on the postgres terminal  Nothing went through after this  as the database is in a state of error  However  just as a quick fix  if you can afford to avoid rollback transaction  Following did the trick for me   COMMIT

User · Answer

I have met this issue   the error comes out since the error transactions hasn t been ended rightly  I found the postgresql transactions of Transaction Control command here   Transaction Control  The following commands are used to control transactions   BEGIN TRANSACTION - To start a transaction   COMMIT - To save the changes  alternatively you can use END TRANSACTION command   ROLLBACK - To rollback the changes    so i use the END TRANSACTION to end the error TRANSACTION  code like this       for key of attribute  command in sql command items            cursor   connection cursor           g logger info  execute command   s     command           try              cursor execute command              rows   cursor fetchall               g logger info  the command  s result is   s     command  rows               result list key of attribute    rows             g logger info  result list is   s     result list           except Exception as e              cursor execute  END TRANSACTION                g logger info  error command   s and error is   s     command  e       return result list

User · Answer

It is an issue with bad sql execution which does not allow other queries to execute until the previous one gets suspended rollback  In PgAdmin4-4 24 there is an option of rollback  one can try this

User · Answer

This is very strange behavior for me  I m surprised that no one thought of savepoints  In my code failing query was expected behavior   from django db import transaction  transaction commit on success def update        skipped   0     for old model in OldModel objects all            try              Model objects create                  group id old model group uuid                  file id old model file uuid                        except IntegrityError              skipped    1     return skipped   I have changed code this way to use savepoints   from django db import transaction  transaction commit on success def update        skipped   0     sid   transaction savepoint       for old model in OldModel objects all            try              Model objects create                  group id old model group uuid                  file id old model file uuid                        except IntegrityError              skipped    1             transaction savepoint rollback sid          else              transaction savepoint commit sid      return skipped

User · Answer

So  I ran into this same issue  The problem I was having here was that my database wasn t properly synced  Simple problems always seem to cause the most angst     To sync your django db  from within your app directory  within terminal  type     python manage py syncdb   Edit  Note that if you are using django-south  running the    python manage py migrate  command may also resolve this issue   Happy coding

User · Answer

I believe  AnujGupta s answer is correct  However the rollback can itself raise an exception which you should catch and handle   from django db import transaction  DatabaseError try      a save   except DatabaseError      try          transaction rollback       except transaction TransactionManagementError            Log or handle otherwise   If you find you re rewriting this code in various save   locations  you can extract-method   import traceback def try rolling back        try          transaction rollback           log warning  rolled back      example handling     except transaction TransactionManagementError          log exception traceback format exc       example handling   Finally  you can prettify it using a decorator that protects methods which use save     from functools import wraps def try rolling back on exception fn        wraps fn      def wrapped  args    kwargs           try              return fn  args    kwargs          except              traceback print exc               try rolling back       return wrapped   try rolling back on exception def some saving method                  model save               Even if you implement the decorator above  it s still convenient to keep try rolling back   as an extracted method in case you need to use it manually for cases where specific handling is required  and the generic decorator handling isn t enough

User · Answer

I ve got the silimar problem  The solution was to migrate db  manage py syncdb or manage py schemamigration --auto  lt table name gt  if you use south

User · Answer

I think the pattern priestc mentions is more likely to be the usual cause of this issue when using PostgreSQL   However I feel there are valid uses for the pattern and I don t think this issue should be a reason to always avoid it  For example   try      profile   user get profile   except ObjectDoesNotExist      profile   make default profile for user user   do something with profile profile    If you do feel OK with this pattern  but want to avoid explicit transaction handling code all over the place then you might want to look into turning on autocommit mode  PostgreSQL 8 2    https   docs djangoproject com en dev ref databases  autocommit-mode  DATABASES  default               you usual options         OPTIONS              autocommit   True            I am unsure if there are important performance considerations  or of any other type

User · Answer

I just had this error too but it was masking another more relevant error message where the code was trying to store a 125 characters string in a 100 characters column   DatabaseError  value too long for type character varying 100    I had to debug through the code for the above message to show up  otherwise it displays  DatabaseError  current transaction is aborted

User · Answer

In Flask you just need to write   curs   conn cursor   curs execute  ROLLBACK   conn commit     P S  Documentation goes here https   www postgresql org docs 9 4 static sql-rollback html

User · Answer

To get rid of the error  roll back the last  erroneous  transaction after you ve fixed your code   from django db import transaction transaction rollback     You can use try-except to prevent the error from occurring   from django db import transaction  DatabaseError try      a save   except DatabaseError      transaction rollback     Refer   Django documentation

User · Answer

In response to  priestc and  Sebastian  what if you do something like this   try      conn commit   except      pass  cursor execute  sql   try       return cursor fetchall   except       conn commit       return None   I just tried this code and it seems to work  failing silently without having to care about any possible errors  and working when the query is good

User · Answer

In my experience  these errors happen this way   try      code that executes bad query         transaction on DB is now bad except      pass    transaction on db is still bad code that executes working query     raises transaction error   There nothing wrong with the second query  but since the real error was caught  the second query is the one that raises the  much less informative  error   edit  this only happens if the except clause catches IntegrityError  or any other low level database exception   If you catch something like DoesNotExist this error will not come up  because DoesNotExist does not corrupt the transaction   The lesson here is don t do try except pass

User · Answer

In Flask shell  all I needed to do was a session rollback   to get past this

User · Answer

just use rollback  Example code  try      cur execute  CREATE TABLE IF NOT EXISTS test2  id serial  qa text     except      cur execute  rollback       cur execute  CREATE TABLE IF NOT EXISTS test2  id serial  qa text

User · Answer

This is what postgres does when a query produces an error and you try to run another query without first rolling back the transaction    You might think of it as a safety feature  to keep you from corrupting your data    To fix this  you ll want to figure out where in the code that bad query is being executed   It might be helpful to use the log statement and log min error statement options in your postgresql server

User · Answer

You only need to run rollback   and that s it   in PostreSQL

[python] DatabaseError: current transaction is aborted, commands ignored until end of transaction block?

Examples related to python

Examples related to django

Examples related to postgresql

Examples related to psycopg2