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

Question

I am seeing the following  truncated  stacktrace in the server log file of JBoss 7 1 1 Final   Caused by  org postgresql util PSQLException   ERROR  current transaction is aborted  commands ignored until end of  transaction block  at org postgresql core v3 QueryExecutorImpl receiveErrorResponse QueryExecutorImpl java 2102  at org postgresql core v3 QueryExecutorImpl processResults QueryExecutorImpl java 1835  at org postgresql core v3 QueryExecutorImpl execute QueryExecutorImpl java 257  at org postgresql jdbc2 AbstractJdbc2Statement execute AbstractJdbc2Statement java 512  at org postgresql jdbc2 AbstractJdbc2Statement executeWithFlags AbstractJdbc2Statement java 374  at org postgresql jdbc2 AbstractJdbc2Statement executeUpdate AbstractJdbc2Statement java 302  at sun reflect NativeMethodAccessorImpl invoke0 Native Method   rt jar 1 6 0 23  at sun reflect NativeMethodAccessorImpl invoke NativeMethodAccessorImpl java 39   rt jar 1 6 0 23  at sun reflect DelegatingMethodAccessorImpl invoke DelegatingMethodAccessorImpl java 25   rt jar 1 6 0 23  at java lang reflect Method invoke Method java 597   rt jar 1 6 0 23  at org postgresql ds jdbc23 AbstractJdbc23PooledConnection StatementHandler invoke AbstractJdbc23PooledConnection java 455  at  Proxy49 executeUpdate Unknown Source    at org jboss jca adapters jdbc WrappedStatement executeUpdate WrappedStatement java 371  at org infinispan loaders jdbc TableManipulation executeUpdateSql TableManipulation java 154   infinispan-cachestore-jdbc-5 1 2 FINAL jar 5 1 2 FINAL      154 more   Inspecting the Postgres log file reveals the following statements   STATEMENT   SELECT count    FROM ISPN MIXED BINARY TABLE configCache ERROR   current transaction is aborted  commands ignored until end of transaction block STATEMENT   CREATE TABLE ISPN MIXED BINARY TABLE configCache ID COLUMN VARCHAR 255  NOT NULL  DATA COLUMN BYTEA  TIMESTAMP COLUMN BIGINT  PRIMARY KEY  ID COLUMN   ERROR   relation  ispn mixed binary table configcache  does not exist at character 22   I am using the Infinispan shipped with JBoss 7 1 1 Final  which is 5 1 2 Final   So this is what I think is happening    Infinispan attempts to run the SELECT count       statement in order to see if there are any records in the ISPN MIXED BINARY TABLE configCache  Postgres  for some reason  does not like this statement  Infinispan ignores this and plows ahead with the CREATE TABLE statement  Postgres barfs because it still thinks it s the same transaction  which Infinispan has failed to roll back  and this transaction is shafted from the first SELECT count       statement    What does this error mean and any idea how to work around it

User · Answer

I was working with spring boot jpa and fixed by implementing   EnableTransactionManagement  Attached file may help you

User · Answer

I got this error using Java and PostgreSQL doing an insert on a table   I will illustrate how you can reproduce this error  org postgresql util PSQLException  ERROR   current transaction is aborted  commands ignored until end of transaction block  Summary  The reason you get this error is because you have entered a transaction and one of your SQL Queries failed  and you gobbled up that failure and ignored it   But that wasn t enough  THEN you used that same connection  using the SAME TRANSACTION to run another query   The exception gets thrown on the second  correctly formed query because you are using a broken transaction to do additional work   PostgreSQL by default stops you from doing this  I m using  PostgreSQL 9 1 6 on x86 64-redhat-linux-gnu  compiled by gcc  GCC  4 7 2 20120921  Red Hat 4 7 2-2   64-bit quot   My PostgreSQL driver is  postgresql-9 2-1000 jdbc4 jar Using Java version   Java 1 7 Here is the table create statement to illustrate the Exception  CREATE TABLE moobar       myval   INT     Java program causes the error  public void postgresql insert         try                 connection setAutoCommit false      start of transaction                   Statement statement   connection createStatement                     System out println  quot start doing statement execute quot                     statement execute                   quot insert into moobar values  quot                     quot  this SQL statement fails  and it  quot                     quot is gobbled up by the catch  okfine     quot                    The above line throws an exception because we try to cram           A string into an Int   I Expect this  what happens is we gobble            the Exception and ignore it like nothing is wrong            But remember  we are in a TRANSACTION   so keep reading           System out println  quot statement execute done quot                     statement close                       catch  SQLException sqle                System out println  quot keep on truckin  keep using  quot                     quot the last connection because what could go wrong  quot                   try          Statement statement   connection createStatement                     statement executeQuery  quot select   from moobar quot               This SQL is correctly formed  yet it throws the             transaction is aborted  SQL Exception  why   Because            A   you were in a transaction            B   You ran a SQL statement that failed            C   You didn t do a rollback or commit on the affected connection                     catch  SQLException sqle                sqle printStackTrace                 The above code produces this output for me  start doing statement execute  keep on truckin  keep using the last connection because what could go wrong   org postgresql util PSQLException     ERROR  current transaction is aborted  commands ignored until    end of transaction block  Workarounds  You have a few options   Simplest solution   Don t be in a transaction   Set the connection setAutoCommit false   to connection setAutoCommit true     It works because then the failed SQL is just ignored as a failed SQL statement    You are welcome to fail SQL statements all you want and PostgreSQL won t stop you   Stay being in a transaction  but when you detect that the first SQL has failed  either rollback re-start or commit restart the transaction   Then you can continue failing as many SQL queries on that database connection as you want   Don t catch and ignore the Exception that is thrown when a SQL statement fails   Then the program will stop on the malformed query   Get Oracle instead  Oracle doesn t throw an exception when you fail a query on a connection within a transaction and continue using that connection    In defense of PostgreSQL s decision to do things this way    Oracle was making you soft in the middle letting you do dumb stuff and overlooking it

User · Answer

Check the output before the statement that caused current transaction is aborted  This typically means that database threw an exception that your code had ignored and now expecting next queries to return some data   So you now have a state mismatch between your application  which considers things are fine  and database  that requires you to rollback and re-start your transaction from the beginning   You should catch all exceptions and rollback transactions in such cases   Here s a similar issue

User · Answer

I just encounter the same error  I was able to figure out the root cause by enabling the log statement and log min error statement in my local PostgreSQL    I Referred this

User · Answer

You need to rollback  The JDBC Postgres driver is pretty bad  But if you want to keep your transaction  and just rollback that error  you can use  savepoints   try    stmt   connection createStatement     savePoint   connection setSavepoint  sp01     result    stmt executeUpdate sentence   gt  0    catch  Exception e    if   savePoint  null    connection rollback  savePoint         Read more here   http   www postgresql org docs 8 1 static sql-savepoint html

User · Answer

There s been some work done on the postgresql JDBC Driver  related to this behaviour  see https   github com pgjdbc pgjdbc pull 477  It is now possible  by setting autosave always in the connection  see https   jdbc postgresql org documentation head connect html   to avoid the  current transaction is aborted  syndroma  Overhead due to handling a savepoint around the statement execution is kept very low  see link above for details

User · Answer

Change the isolation level from repeatable read to read committed

User · Answer

In my case i was getting this error because my file was corrupt  While iterating the records of files it was giving me the same error   May be in future it will help to anyone  That s the only reason to post this answer

User · Answer

Set conn setAutoCommit false  to conn setAutoCommit true   Commit the transactions before initiating a new one

User · Answer

Try this COMMIT   I run that in pgadmin4  It may help  It has to do with the previous command stopping prematurely

User · Answer

I think that the best solution is to use java sql Savepoint  Before you execute a query which can throw SQLException  use the method Connection setSavepoint    and if an exception is thrown you only rollback to this savepoint  not the whole transaction  Example code  Connection conn   null  Savepoint savepoint   null  try       conn   getConnection        savepoint   conn setSavepoint          execute some query   catch SQLException e        if conn    null  amp  amp  savepoint    null            conn rollback savepoint           finally      if conn    null          try             conn close            catch SQLException e

User · Answer

I had the same issue but then realised there is a table with the same name in the database  After deleting that I was able to import the file

User · Answer

This is very weird behavior of PostgreSQL  it is even not   in-line with the PostgreSQL philosophy of forcing the user to make everything explicit  - as the exception was caught and ignored explicitly  So even this defense does not hold  Oracle in this case behaves much more user-friendly  and  as for me  correctly - it leaves a choice to the developer

User · Answer

The issue has been fixed in Infinispan 5 1 5 CR1  ISPN-2023

User · Answer

The reason for this error is that there are other database before the wrong operation led to the current database operation can not be carried out i use google translation to translate my chinese to english

User · Answer

In Ruby on Rails PG  I had created a migration  migrated my DB  but forgot to restart my development server  I restarted my server and it worked

User · Answer

I was working with spring boot jpa and fixed by implementing   EnableTransactionManagement  Attached file may help you

User · Answer

I use spring with  Transactional annotation  and I catch the exception and for some exception I will retry 3 times   For posgresql  when got exception  you can t use same Connection to commit any more You must rollback first   For my case  I use the DatasourceUtils to get current connection and call connection rollback   manually  And the call the method recruive to retry

User · Answer

I am using JDBI with Postgres  and encountered the same problem  i e  after a violation of some constraint from a statement of previous transaction  subsequent statements would fail  but after I wait for a while  say 20-30 seconds  the problem goes away    After some research  I found the problem was I was doing transaction  manually  in my JDBI  i e  I surrounded my statements with BEGIN    COMMIT  and it turns out to be the culprit   In JDBI v2  I can just add  Transaction annotation  and the statements within  SqlQuery or  SqlUpdate will be executed as a transaction  and the above mentioned problem doesn t happen any more

User · Answer

This can happen if you are out of disk space on the volume

[postgresql] PSQLException: current transaction is aborted, commands ignored until end of transaction block

Examples related to postgresql

Examples related to jboss

Examples related to infinispan