SELECT AFTER INSERT

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);
}
}