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 blockHow 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 blockHow 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:
Show quoted text
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 blockHow 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.