Re: Trouble with exception

samantha mahindrakar wrote:
> Hi....
> Iam trying to insert records into a table......when an integrity
> constarint violation occurs the exception is caught........but i dont
> want the whole thing to be rolled back or stopped because of one
> exception.

[snip]
> Is there a way i can just skip the record that causes the violation> and insert the rest of the records into the
table???

One option is to add an additional constraint to the INSERT query that 
excludes rows that'd provoke the constraint voliation error. This might 
make the insert more expensive. If the constraint is a foreign key 
constraint it might be quite a bit more expensive as you'll be executing 
every foreign key constraint check twice*. In practice this probably 
won't matter much.

Another alternative is to convert your INSERT to a looping PL/PgSQL 
function that uses an EXCEPTION block to trap insert errors row-by-row. 
Note however that 8.2 and earlier have awful performance when lots of 
rows are inserted in a single transaction using an exception block 
around each INSERT. Even with newer versions it might still not perform 
great, though changes were included in 8.3 to improve performance in 
this case.

To me it seems much better to just avoid attempting to insert the 
invalid records in the first place by using an appropriate WHERE 
constraint on your INSERT query.


* It'd be truly fantastic if the optimizer could infer that the WHERE 
clause on an INSERT/UPDATE/DELETE prevent a given constraint from being 
volated and could prevent the normal execution of the constraint check 
when the insert/update/delete ran. However, I can imagine that might be 
*really* hard to implement, and only useful for a very rare sort of 
query. Even then it'd only make a difference when the constraint check 
was fairly expensive.

--
Craig Ringer