From: | Julian Legeny <legeny(at)softhome(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Select after insert to the unique column |
Date: | 2004-12-08 13:50:04 |
Message-ID: | 10314528921.20041208145004@opensubsystems.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
}
...
From | Date | Subject | |
---|---|---|---|
Next Message | Don Isgitt | 2004-12-08 14:41:19 | No mailing list posts |
Previous Message | cathy.hemsley | 2004-12-08 10:54:49 | Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities |