Re: Trouble with exception - Mailing list pgsql-sql

Thanks Craig ....that reply really helped.

I had used second approach where i converted the INSERT into a loop
with an exception block.....but as you said it does take a lot of
time. So i changed the INSERt query itself to exclude the records that
were causing the problem. It sad that the query is quite
expensive....but better than the looping.

Iam posting the query......if anybody suggest a better way of writting
it...i would be great.

INSERT INTO table1     (SELECT
lane_id,speed,volume,occupancy,quality,measurement_start,measurement_end,effective_date,expiration_date
FROM table2     WHERE lane_id IN(select lane_id from table3 where inactive is  null
)AND (volume=255 OR speed=255 OR occupancy=255 OR occupancy>=100 OR
volume>52 OR volume<0 OR speed>120 OR speed<0)     AND date_part('hour', measurement_start) between 5 and 23     AND
date_part('day',measurement_start)='||theDate'||    AND (lane_id,measurement_start) NOT IN (SELECT lane_id,
 
measurement_start from table1);

The query is trying to insert a set of recordsfrom table2 into
table1.The NOT IN i used to exclude the records that can cause
integrity constraint errors.
I was wondering if there was a better way of doing this.

Thanks
Sam



On 6/6/08, Craig Ringer <craig@postnewspapers.com.au> wrote:
> 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
>