Re: Update problem

Iam sorry for the previous mail......it was in complete. Please do not
consider it.
I think i could figure out the problem for the updates not happening.
Following is the function that does the update :
BEGIN
IF flag=1 THEN
tempQuery:='UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start >=
date_trunc(''minute'',timestamp'''||measurement_start||''') AND
measurement_start <
date_trunc(''minute'',timestamp'''||measurement_start + interval '1
minute'||''')';
EXECUTE 'UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start >=
date_trunc(''minute'',timestamp'''||measurement_start||''') AND
measurement_start <
date_trunc(''minute'',timestamp'''||measurement_start + interval '1
minute'||''')';
RAISE NOTICE 'UPDATE QUERY %',tempQuery;
RAISE NOTICE 'FOUND %',FOUND;
ELSE
tempQuery:='UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start =
'''||measurement_start||'''';
EXECUTE 'UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start =
'''||measurement_start||'''';
RAISE NOTICE 'UPDATE QUERY %',tempQuery;
RAISE NOTICE 'FOUND %',FOUND;
END IF;

--Update the detector health data
EXECUTE 'INSERT into lane_detector_health (lane_id,
measurement_start,detector_status)
values('||lane||','''||measurement_start||''','||health||')';
EXCEPTION WHEN integrity_constraint_violation THEN
RAISE NOTICE 'Imputation for lane % at time % has been already
imputed',lane,measurement_start;
END;

There is an insert staement at the end that has an exception block. I
think whenever an exception is getting caught the data is getting
rolled back and hence the updates are getting rolled back.
I wrote the exception thinking that the roll back happens for only one
insert statement if it happens........i dont know if i have judged the
scope of the exception block wrong...

Could someone please clear me on this.

Thanks
Sam

On 6/3/08, samantha mahindrakar <sam(dot)mahindrakar(at)gmail(dot)com> wrote:
> I tried getting the output of the execute statements by printing the
> FOUND variable. It is returning the value as false.
> However i used PEFORM instead of EXECUTE for the update statement. It
>
> On 6/3/08, samantha mahindrakar <sam(dot)mahindrakar(at)gmail(dot)com> wrote:
> > Hi....
> > Iam facing a strange issue....
> > One of the functions in my program is running an update statement. The
> > statement is running cross-schema. What i mean is that the program
> > resides in one schema where as it updates a table from another schema.
> > How ever these scehmas are on the same database.
> > The program runs correctly and also prints out the update statement.
> > But it never actually updates the table.....neither does it fail.
> > However when i run one of the update statements individually in the
> > query tool...the update happens.
> > Iam assuming that this is not a problem with the permissions either
> > since the permission for the table to be updated is set to public.
> > iam pasting the update statement for reference:
> >
> > EXECUTE 'UPDATE '||thepartition||' SET
> > volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
> > WHERE lane_id='||lane||' and measurement_start =
> > '''||measurement_start||'''';
> >
> >
> > Thanks
> > Sam
> >
>