Rob Messer wrote:
> This is really how transactions are supposed to work -- to maintain
> database integrity, in a transaction either everything should go through
> or nothing. It would work the same way in Oracle and other databases
> with transaction control.
Here I fully contradict. Yes, in a transaction either everything should
go through or nothing, but it should be my decision what "everything"
is. The example I wrote works inside our application with Oracle,
Informix, Sybase Enterprise Server and Sybase SQL Anywhere without any
problems since years.
> One solution would seem be to do checking
> before the insert to make sure you are not inserting duplicate keys.
But if it would work, as it should (in my opinion and like it does in
any other dbms I know), I can write the following code, which is much
more elegant and more effective than explicite checking of conditions:
begin work
update table y
insert into table z
try
insert into table x
commit
catch pk_constraint_violation
update table x
commit
catch the rest
rollback
print error message
And this contradicts not the idea of a transaction. It's the programmers
choice, when the transaction is committed and when it is rollbacked. And
using exception handling this way leads to very nice and readable code,
whereas using nested if() conditions is bad code in my eyes.
> Or you could perhaps break the transaction into smaller parts and then just
> ignore the errors if you don't care about them. In any event PostgreSQL
> seems to be doing what it should be doing here.
Ok, to use your words ;) Is it possible to trick PostgreSQL into doing
what it not should do: not aborting the transcation?
Thanks,
Joern
--
Joern Reder -- Software Development, dimedis GmbH, 50672 Koeln
http://www.dimedis.de/ http://spirit.dimedis.de/
supporting: http://www.zyn.de/ http://www.netcologne.de/~nc-joernre/
CPAN: http://www.perl.com/CPAN/modules/by-module/CIPP/JRED/
