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?

Just if you came here like me searching for the above `PSQLException: current transaction is aborted...` (`25P02`) and maybe also `JPA` or `Hibernate`. Finally it was due to our (nice!) **Logback** usage fed with a `toString()`-overloaded DAO object that caused the error and was nicely swallowed (but accidentially unnoticed by me): `log.info( "bla bla: {}", obj )` produced **`bla bla: [FAILED toString()]`**. changing it to `log.info( "bla bla: {}", String.valueOf( obj )` made it null-safe, but not swallowing it and thus leaving the transaction open failing on an unrelated query. — Andreas Covidiot, Sep 02 '15 at 10:14
I was getting same type of error. I had to release the connection before the sql. Mine code was connection.commit() — md. ariful ahsan, Sep 05 '19 at 12:07
I have answered here https://stackoverflow.com/a/65172183/3323007 and it works! — Zvi, Dec 06 '20 at 19:29
There are 2 ways to fix this. 1) run ROLLBACK; this will sort the issue with the current tab, 2) open a new tab and run the same command. Try to add try catch in the query running. — althaf a s, Nov 25 '21 at 04:54

score 310 · Answer 1 · edited Sep 15 '20 at 18:02

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".

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("start doing statement.execute");
        
        statement.execute(
                "insert into moobar values(" +
                "'this SQL statement fails, and it " +
                "is gobbled up by the catch, okfine'); ");
     
        //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("statement.execute done");
        
        statement.close();
        
    }
    catch (SQLException sqle)
    {
        System.out.println("keep on truckin, keep using " +
                "the last connection because what could go wrong?");
    }
    
    try{
        Statement statement = connection.createStatement();
        
        statement.executeQuery("select * from moobar");

        //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.

Lol @ Option 4... I had done quite a bit of development in Oracle, and recently started using Postgres... it's really annoying that Postgres does this, and now we have to be really rewrite a big bulk of our program that we are porting from Oracle to Postgres. Why isn't there an option like the first to make it behave like Oracle but **without the auto-commit**? — ADTC, Sep 02 '13 at 04:57
Found out after some trials that **Option 2** is the closest you can get to Oracle behavior. If you need to issue multiple updates, and one failure should not stop subsequent updates, simply call `rollback()` on the `Connection` when an `SQLException` is caught. [_Anyway_ I realized this is in-line with the PostgreSQL philosophy of forcing the user to make everything explicit, whereas Oracle has the philosophy of implicitly taking care of a lot of things.] — ADTC, Sep 05 '13 at 02:00
Is there a way of checking the Connection object (in Java) to see if a transaction has failed? — Roger C S Wernersson, Feb 20 '15 at 16:15
*Option 2* contains impossible branch `or commit/restart the transaction`. As I can see there is no way to commit after exception. When I try to commit - PostgreSQL do `rollback` — turbanoff, Sep 28 '15 at 12:51
I can confirm the problem raised by @turbanoff. This can also be reproduced directly with `psql`. (1) start a transaction, (2) issue some valid statements, (3) issue an invalid statement, (4) commit --> psql will rollback rather than committing. — Alphaaa, Apr 05 '17 at 13:34
https://www.postgresql.org/message-id/op.ur57x9ue33x80h%40insanity.lain.pl an interesting discussion of this topic. If this issue is triggered by a constraint violation, the PostgreSQL devs recommend checking for a conflict ahead of time (query before update/insert) or using `savepoints` to roll back to the point before the update/insert. See https://stackoverflow.com/a/28640557/14731 for sample code. — Gili, Jan 03 '18 at 03:07
Not mentioning SavePoints as an option is a huge hole in this answer — Edd, Jan 10 '20 at 18:05

score 44 · Answer 2 · edited May 23 '17 at 12:26

44

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.

edited May 23 '17 at 12:26

Community

1
1

answered May 01 '12 at 15:36

vyegorov

21,787
7
59
73

That's great, except in this case it would be Infinispan, a 3rd-party library, doing the talking to Postgres, and not my code. – Jimidy May 01 '12 at 15:39
Well, the situation still the same — transaction has to be rolled back. Maybe check if there's a newer version of the library you're using or rise the issue within their bug tracker. If you'll find the exact `SQL` that caused the issue, you'll have some field to eliminate the issue using PostgreSQL extensibility. – vyegorov May 01 '12 at 15:43
Seems like you've confirmed my suspicions - I am going to look at the Infinispan 5.1.2 source now. – Jimidy May 01 '12 at 15:47
To be fair, in the TableManipulation class, there is a try-catch around the attempt to run select count(*).... Perhaps the Postgres driver is not throwing one of the expected exceptions. I will hook up a debugger to JBoss to try to find out more. – Jimidy May 01 '12 at 16:07
The Infinispan code in question was suggested in this bug: https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin.system.issuetabpanels:changehistory-tabpanel I have attached a debugger to it running on a live JBoss 7.1.1 instance and Postgres is throwing exceptions in the right places. Perhaps it is the JdbcUtil.safeClose() statements that are not doing their job. I shall raise it with Infinispan. – Jimidy May 01 '12 at 16:27

score 24 · Answer 3 · edited Sep 15 '20 at 18:06

24

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 && savepoint != null) {
        conn.rollback(savepoint);
    }
} finally {
   if(conn != null) {
      try {
          conn.close();
      } catch(SQLException e) {}

   }
}

edited Sep 15 '20 at 18:06

xehpuk

7,814
3
30
54

answered Feb 21 '15 at 00:30

Michał Orliński

1,308
13
15

I accidentally downvoted somehow, only just noticed. Wasn't intentional, I can't undo unless answer is edited. – cerberos Jun 03 '15 at 11:49
1

The savepoint way is the actual solution. Works for me also in environment of PHP, Doctrine2 and Postgres (9.5). Thanks – helvete Jan 16 '19 at 12:39

score 17 · Answer 4 · answered Feb 13 '18 at 16:28

17

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).

answered Feb 13 '18 at 16:28

thierry masson

171
1
3

2

IMO, this should be accepted answer on this question. Also along with 'autosave=always', it's advisable to set 'cleanupSavepoints=true' to avoid running out of shared buffers. – v3nM Aug 03 '21 at 16:05
works like a charm. put in connection string like jdbc://localhost:5432/xxxx?autosave=always – dian mushkov Sep 13 '22 at 13:34

score 15 · Answer 5 · answered Apr 23 '20 at 14:24

15

Try this COMMIT;

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

answered Apr 23 '20 at 14:24

Phillip Kigenyi

1,359
14
21

Solved problem for me) – Frankie Drake Mar 05 '21 at 10:09

score 6 · Answer 6 · answered Feb 13 '16 at 05:27

6

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.

answered Feb 13 '16 at 05:27

thedanotto

6,895
5
45
43

That was my case as well. Thought it should be something stupid, because I didn't really try to do anything that complicated. – Tashows Aug 29 '18 at 15:12

score 5 · Answer 7 · answered Jun 09 '17 at 02:37

5

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）

answered Jun 09 '17 at 02:37

管浩浩

63
1
4

score 4 · Answer 8 · answered Nov 04 '15 at 05:16

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) > 0;
} catch (Exception e){
 if (_savePoint!=null){
 connection.rollback(_savePoint);
}
}

Read more here:

http://www.postgresql.org/docs/8.1/static/sql-savepoint.html

score 3 · Answer 9 · answered May 30 '12 at 10:26

3

The issue has been fixed in Infinispan 5.1.5.CR1: ISPN-2023

answered May 30 '12 at 10:26

Dan Berindei

7,054
3
41
48

score 3 · Answer 10 · answered Feb 27 '18 at 23:44

3

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.

answered Feb 27 '18 at 23:44

S.Perera

874
3
9
24

This was my issue, the tables for me were across two different schemas. – tomato Oct 23 '19 at 19:58

score 3 · Answer 11 · answered Aug 22 '22 at 13:57

3

I faced this error in my DB application tool because there is one uncommited transaction. And i'm trying to run a select query. so that gave me this error. You can fix these either running

commit;

or

rollback;

answered Aug 22 '22 at 13:57

Navneet Singh

211
1
9

score 1 · Answer 12 · answered Jan 15 '14 at 15:19

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.

score 1 · Answer 13 · answered May 11 '15 at 19:34

1

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

answered May 11 '15 at 19:34

gregb

31
1

I realize this is not the most common cause, but this was the case on a server that I was asked to troubleshoot. So I think this should be listed as a potential cause. – gregb Apr 14 '16 at 01:49

score 1 · Answer 14 · answered Jan 18 '22 at 10:42

1

For me issues was, driver wasn't installed. I downloaded the driver and pasted in ~/Library/Tableau/Driver(mac) folder & it worked.

answered Jan 18 '22 at 10:42

Umesh Jiddi

21
2

score 0 · Answer 15 · edited May 23 '17 at 12:10

0

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

edited May 23 '17 at 12:10

Community

1
1

answered Aug 15 '16 at 06:42

virtualpathum

753
2
11
23

score 0 · Answer 16 · answered Aug 28 '17 at 22:39

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!

score 0 · Answer 17 · answered Nov 19 '19 at 13:05

0

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.

answered Nov 19 '19 at 13:05

Bharti Rawat

1,949
21
32

score 0 · Answer 18 · answered Jan 15 '20 at 03:25

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.

score 0 · Answer 19 · answered Apr 20 '23 at 23:54

0

If you are using a SQL client like dbeaver or pgAdmin, just click on Disconnect and connect again from the squema or database.

answered Apr 20 '23 at 23:54

nativelectronic

646
4
11

score -1 · Answer 20 · answered Jan 24 '15 at 14:46

-1

Change the isolation level from repeatable read to read committed.

answered Jan 24 '15 at 14:46

ericj

2,138
27
44

score -1 · Answer 21 · answered Apr 21 '20 at 16:13

-1

I was working with spring boot jpa and fixed by implementing @EnableTransactionManagement

Attached file may help you.

answered Apr 21 '20 at 16:13

Shahid Hussain Abbasi

2,508
16
10

score -4 · Answer 22 · answered Apr 21 '20 at 16:10

-4

I was working with spring boot jpa and fixed by implementing @EnableTransactionManagement

Attached file may help you.

answered Apr 21 '20 at 16:10

Shahid Hussain Abbasi

2,508
16
10

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

22 Answers22

Linked