> Or do you really want to commit the previous statements in the
> transaction rather than throw them away? If so, you didn't really need
> the transaction block there anyway, since you are willing to accept a
> partial result.
Actually the postgresql behavior on error has been nagging on me also (OK, only a little bit).
Yes, the usual behavior is for the application to decide if it wants to roll back
all other work on error or go on and correct the error and then commit all work
even if one part did not succeed.
This is what is usually done, and actually helps performance since it saves one select:
begin work;
do arbitrary work
insert /* violates primary key constraint --> ERROR */
on error do instead update the offending row
do other work
commit work;
The decision on whether to do the update or the insert first is based on frequency of
successful events for the first statement.
(more inserts --> insert first, more updates --> update first)
All commercial DBMS's handle it this way, it is probably even in ANSI.
Andreas
