> 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
	


Tue, 22 Sep 1998 08:46:34 +0200
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
Re: [HACKERS] Errors inside transactions