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