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 :
BEGINIF flag=1 THENtempQuery:='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;ELSEtempQuery:='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 dataEXECUTE 'INSERT into lane_detector_health (lane_id,
measurement_start,detector_status)
values('||lane||','''||measurement_start||''','||health||')';EXCEPTION WHEN integrity_constraint_violation THENRAISE
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.mahindrakar@gmail.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.mahindrakar@gmail.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
> >
>
