PostgreSQL: current transaction is aborted, commands ignored until end of transaction block; 25P02 in_failed_sql_transaction - resolved

Once the Connection is set to auto commit false, all the SQL statement executions using this same Connection are in one transaction block.

In PostgreSQL, if one of the SQL statement execution failed, you ignored it, and continued executing other SQL statements, it throws the error, "org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block." The exception SQL state is 25P02, in_failed_sql_transaction.

For example, you have a FRUIT table.

NAME            PRICE            LOCATION            DESCRIPTION

Apple             1.64                 Florida                     Very popular

Orange           0.89                 Florida                     Winter fruit

The following code will incur such an error.

public void updateFruit(Connection con) {

      try {

              PreparedStatement ps = con.prepareStatement("Update FRUIT set PRICE = 'Ten dollars' WHERE NAME = 'Apple'");

              //It will fail for assigning price a string instead of a number

             ps.executUpdate();

      } catch (SQLException e) {

            System.out.println("Failed to update");

      }

      try {

            PreparedStatement ps2 = con.prepareStatement("Insert into FRUIT values ('Pineapple', 2.15,'Florida', 'Delicious');

            ps2.executeUpdate();

      } catch(SQLException e2) {

            e2.printStackTrace(System.out);

      }

}

The output of the above code is below.

Failed to update

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

To Fix it, set save points to rollback when query fails. Only queries after the save point are rolled back.

public void updateFruit(Connection con) {

      Savepoint sp = null;

      try {

            sp = con.setSavepoint();

            PreparedStatement ps = con.prepareStatement("Update FRUIT set PRICE = 'Ten dollars' WHERE NAME = 'Apple'");

              //It will fail for assign price a string instead of a number

             ps.executUpdate();

      } catch (SQLException e) {
            try {

                  con.rollback(sp);
            } catch(SQLException se) {
                  System.out.println("Failed to rollback.");
            }

            System.out.println("Failed to update");

      }

      try {

            sp = con.setSavepoint();

            PreparedStatement ps2 = con.prepareStatement("Insert into FRUIT values ('Pineapple', 2.15,'Florida', 'Delicious');

            ps2.executeUpdate();

      } catch(SQLException e2) {
            try {

                   con.rollback(sp);
                   con.releaseSavepoint();
             }catch(SQLException se) {
                    System.out.println("Failed to rollback.");
             }

            e2.printStackTrace(System.out);

      }

}

-------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.