Thread: Select after insert to the unique column
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;
}
...
On Wed, Dec 08, 2004 at 14:50:04 +0100, Julian Legeny <legeny@softhome.net> wrote: > Hello, > > 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? Depending on what you really want to do, you could do each insert in its own transaction. If you don't want any of the inserts to succeed if there are problems, then you should do the counting in the application doing the inserts.
If you attempted the inserts within a single transaction and any of them fail, they will all fail. The server will automatically undo any and all changes made by the transaction, and any further steps in the transaction will simply result in the error message you are getting. You will not be able to (successfully) issue any further database commands until you end the transaction and start a new one. On Dec 11, 2004, at 2:29 PM, Bruno Wolff III wrote: > On Wed, Dec 08, 2004 at 14:50:04 +0100, > Julian Legeny <legeny@softhome.net> wrote: >> Hello, >> >> 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? > > Depending on what you really want to do, you could do each insert in > its > own transaction. > > If you don't want any of the inserts to succeed if there are problems, > then > you should do the counting in the application doing the inserts. > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
Thank you for your answer. I think it's very interesting behaviour. Is it a feature or bug ? I have try this my jUnit test for another DB systems (e.g. Oracle 9i, MS SQL Server 2000, MySQL, DB2, Sybase, SAP DB) and it works for each of these databases (it was possible tu run next command successfully after an exception occured before). With baset regards, Julian Legeny Monday, December 13, 2004, 4:26:24 PM, you wrote: FDEJ> If you attempted the inserts within a single transaction and any of FDEJ> them fail, they will all fail. The server will automatically undo any FDEJ> and all changes made by the transaction, and any further steps in the FDEJ> transaction will simply result in the error message you are getting. FDEJ> You will not be able to (successfully) issue any further database FDEJ> commands until you end the transaction and start a new one. FDEJ> On Dec 11, 2004, at 2:29 PM, Bruno Wolff III wrote: >> On Wed, Dec 08, 2004 at 14:50:04 +0100, >> Julian Legeny <legeny@softhome.net> wrote: >>> Hello, >>> >>> 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? >> >> Depending on what you really want to do, you could do each insert in >> its >> own transaction. >> >> If you don't want any of the inserts to succeed if there are problems, >> then >> you should do the counting in the application doing the inserts. >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> >>
On Mon, Dec 13, 2004 at 17:04:17 +0100, juleni@livetrade.cz wrote: > Thank you for your answer. I think it's very interesting behaviour. Is > it a feature or bug ? Until version 8 (which is in release candidate status now), there was no way to recover from an error within a transaction other than aborting the transaction. With version 8 you will be able to use savepoints to allow for recovery from errors within a transaction. The best answer to your question above, is that it is a limitation and not really a feature and definitely not a bug.