[GENERAL] Select after insert to the unique column

[Julian Legeny]

Hello,

   I have a following table with unique column:

   CREATE TABLE UNIQUE_COLUMN_TEST (
     TEST_ID INTEGER,
     CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID)
   )


   Then I insert few records into the table, and then I try to insert
   duplicate record. There is correct error occured:

   org.postgresql.util.PSQLException:
   ERROR: duplicate key violates unique constraint "test_id_uq"


   Then I want to process command
      select count(*) from UNIQUE_COLUMN_TEST
   that I want to know how many records was already inserted before id
   faied.

   But when I try to process that SELECT COUNT(*), there is error
   occured again:
   
   org.postgresql.util.PSQLException:
   ERROR: current transaction is aborted, commands ignored until end of 
transaction block

   How can I solve this?

   Thank you in advance,
   with best regards,

   Julian Legeny

   
   All this work is processed within 1 transaction and here is the
   code:
      
   // insert value
   m_transaction.begin();
   
   try
   {
      Connection connection = null;

      try
      {
         // try to insert 5 correct records
         for (iCounter = 1; iCounter < 6; iCounter++)
         {
            insertStatement = m_connection.prepareStatement(
            "insert into UNIQUE_COLUMN_TEST (TEST_ID) values (?)");
            insertStatement.setInt(1, 100 * iCounter);
   
            insertStatement.executeUpdate();
          }
          // insert duplicite value into unique column
          try
          {
             insertStatement = m_connection.prepareStatement(
             "insert into UNIQUE_COLUMN_TEST (TEST_ID) values (?)");
             insertStatement.setInt(1, 100);
   
             insertStatement.executeUpdate();
           }
           catch (SQLException sqlExc)
           {
              try
              {
                 // THIS EXCEPTION IS EXPECTED
                 // now try to find out how many records were
                 // already inserted befor it failed
                 selectStatement = m_connection.prepareStatement(
                 "select count(*) from UNIQUE_COLUMN_TEST");

                 // !!! AT THE FOLLOWING LINE IT FAILED AGAIN !!!
                 // cause: current transaction is aborted, commands
                 //        ignored until end of transaction block
                 rsResults = selectStatement.executeQuery();
                 
                 if (rsResults.next())
                 {
                    assertEquals("Incorrect number of selected items",
                                 5, rsResults.getInt(1));
                 }
               }
               catch (SQLException sqlExc1)
               {
                  throw new SQLException();
               }
               finally
               {
                  rsResults.close();
               }
            }
         }
         finally
         {
            DatabaseUtils.closeStatement(insertStatement);
            DatabaseUtils.closeStatement(selectStatement);
         }
         m_transaction.commit();
      }
      catch (Throwable throwable)
      {
         m_transaction.rollback();
         throw throwable;
      }

...


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster