Hi!

I do the following with postgresql. First I disable auto-commit, then

create table tab ( col varchar(30), constraint colunq unique (col))");
commit
insert into tab ( col ) values ('zack')
insert into tab ( col ) values ('zack')

The last command throws a UNIQUE VIOLATION (code 23505) as expected. Then I
want to continue with:

insert into tab ( col ) values ('zick')

Then I get a IN FAILED SQL TRANSACTION (code 25P02). So the previous unique
violation somehow made my transaction unusable. I tried the same with
hsqldb/MySQL/Oracle but these do handle the situation gracefully.

Any hints what I can do?

I don't want to start a new transaction right after the unique violation,
because I want to be able to commit or rollback the whole thing later.

I use PostgreSQL 7.4.7, and access it with JDBC (PostgreSQL Native Driver,
PostgreSQL 7.4.5 JDBC3 with SSL (build 216))

Best Regards,
Ralf.

I tried it with version 8.1.3 and the behaviour is exactly the same.

Ralf.

Ralf Wiebicke <ralf (AT) rw7 (DOT) de> wrote:
Sorry, but there is no way to change this behaviour, it is the intended
behaviour.

When there is an error in a transaction, it is automatically aborted, and
you can only roll it back.

I don't know about MySQL (MySQL and transactions?), but Oracle definitely
behaves similarly. The only difference is that Oracle will issue the
ROLLBACK for you and starts a new transaction on the sly, while PostgreSQL
requires you to issue the rollback.

Try the following in Oracle (with autocommit off):

insert into tab ( col ) values ('zock');
insert into tab ( col ) values ('zack');
insert into tab ( col ) values ('zack');
insert into tab ( col ) values ('zick');
select col from tab where col like 'z_ck';

It is best to have only those statements inside one transaction block
that should either all fail or all succeed.

If you have several INSERT statements inside one transaction block to
boost performance, you must be prepared to repeat all the inserts in case
of an error (you can consider this the price you have to pay for increased
performance).

Yours,
Laurenz Albe

Hi Laurenz!

Sorry, I managed to start a discussion on two different places, because I
was a bit impatient. The second place is here:

http://archives.postgresql.org/pgsql...9/msg00956.php


tried MyISAM, because I read about the lack of transaction, foreign key
constraints etc.

As far as I understood one of the postings on the other place

http://archives.postgresql.org/pgsql...9/msg00988.php

Oracle can do this. So the table would contain zock, zack *and* zick at the
end. I will try this myself on all my databases supported up to now.

Best regards,
Ralf.

Hi Laurenz!

I tried this:

insert into tab ( col ) values ('zock');
insert into tab ( col ) values ('zack');
insert into tab ( col ) values ('zack');
insert into tab ( col ) values ('zick');
commit;
select col from tab;

All my previously used databases do as I would expect: throw an error on the
second insertion of "zack" and the final select return zack, zock and zick.
"previously used databases" means hsqldb 1.8, mysql 4 and 5 with the innodb
engine, Oracle 9 and 10.

Best regards,
Ralf.

Ralf Wiebicke <ralf (AT) rw7 (DOT) de> wrote:
I must admit that I was too hasty in my assumption of Oracle's behaviour:
you are right, and Oracle has something it calls 'Statement-Level
Rollback' as explained in
http://download-uk.oracle.com/docs/c....htm#sthref615

I looked, and there is a way to get something similar in PostgreSQL by
using savepoints. If you issue a SAVEPOINT prior to every INSERT, you
can ROLLBACK TO SAVEPOINT when you encounter an error.

Would that help with what you want to do?

Yours,
Laurenz Albe

Hi Laurenz!

Sorry for the late answer, I was on holiday.

Yes. Savepoints where already recommended in the postgresql mailing list. I
implemented this and it solves my problem.

Best regards,
Ralf.