SELECT AFTER INSERT - Mailing list pgsql-general

Hello,

   I'm using postgreSQL 8.0 beta1 and JDK 1.4.

   I have following problem:
I have create test table that contains only unique column :

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

Within one transaction I have insert a few records into the table.
After that I have insert one more record that already exists within
the table. It gives me constraint exception - it's OK. This exception is
catched within the catch(SQLException) block. In this block then I have
try to do select into the table, but it gives me next exception:

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


   Is this correct postgres behaviour? How can I solve this problem?

   Thank you in advance for your advice,
   with best regards,

   Julian Legeny



   Here is my jUnit test:

   /**
    * Test for selecting data after inserting records and then inserting duplicate record
    * into the unique table column.
    *
    * @throws Throwable - an error has occured during test
    */
   public void testSelectAfterInsertIntoUniqueColumn(
   ) throws Throwable
   {
      final String INSERT_VALUE = "insert into UNIQUE_COLUMN_TEST (TEST_ID) values (?)";
      final String SELECT_VALUE = "select count(*) from UNIQUE_COLUMN_TEST";
      final String DELETE_ALL = "delete from UNIQUE_COLUMN_TEST";

      PreparedStatement insertStatement = null;
      PreparedStatement deleteStatement = null;
      PreparedStatement selectStatement = null;
      ResultSet         rsResults       = null;
      int               iDeletedCount   = 0;
      int iCounter;

      try
      {
         //******************************************************************
         // Try to select original record to verify that the database is in OK state
         m_transaction.begin();
         try
         {
            deleteStatement = m_connection.prepareStatement(DELETE_ALL);

            iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);

            m_transaction.commit();
         }
         catch (Throwable throwable)
         {
            m_transaction.rollback();
            throw throwable;
         }

         assertEquals("No records should be initially in the database.",
                             0, iDeletedCount);

         // insert value
         m_transaction.begin();
         try
         {
            Connection connection = null;

            try
            {
               // try to insert 5 records
               for (iCounter = 1; iCounter < 6; iCounter++)
               {
                  insertStatement = m_connection.prepareStatement(INSERT_VALUE);
                  insertStatement.setInt(1, 100 * iCounter);

                  insertStatement.executeUpdate();
               }
               // insert duplicite value into unique column
               try
               {
                  insertStatement = m_connection.prepareStatement(INSERT_VALUE);
                  insertStatement.setInt(1, 100);

                  insertStatement.executeUpdate();
               }
               catch (SQLException sqlExc)
               {
                  try
                  {
                     // it should be exception here
                     selectStatement = m_connection.prepareStatement(SELECT_VALUE);
                     rsResults = selectStatement.executeQuery();

                     if (rsResults.next())
                     {
                        assertEquals("Incorrect number of selected items",
                                     5, rsResults.getInt(1));
                     }
                  }
                  catch (SQLException sqlExc1)
                  {
                     // selectStatement gives me next exception
                     throw new SQLException();
                  }
                  finally
                  {
                     rsResults.close();
                  }
               }
            }
            finally
            {
               DatabaseUtils.closeStatement(insertStatement);
               DatabaseUtils.closeStatement(selectStatement);
            }
            m_transaction.commit();
         }
         catch (Throwable throwable)
         {
            m_transaction.rollback();
            throw throwable;
         }
      }
      finally
      {
         // delete inserted data
         m_transaction.begin();
         try
         {
            deleteStatement = m_connection.prepareStatement(DELETE_ALL);
            iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
            m_transaction.commit();

         }
         catch (Throwable throwable)
         {
            m_transaction.rollback();
            throw throwable;
         }
         finally
         {
            DatabaseUtils.closeStatement(deleteStatement);
         }
         assertEquals("Exactly 5 records with data shoud have been deleted.",
                             5, iDeletedCount);
      }
   }