Hi all,
I am having problems with concurrent transactions. For my application I am running the following commands -
SELECT pointid,pointtype,pointname,createtime FROM pointtable p ,ebihistory e WHERE p.pointid = e.pointnumber AND e.flag='0'
After executing this query, I do an update as follows -
UPDATE ebihistory SET flag='1'
But inbetween the SELECT and the UPDATE query, few more tuples are added into ebihistory table with flag='0'. So the UPDATE command updates the flag field of the newly inserted tuples also. As a result I am not able to track the newly inserted tuples.
As a work around I tried to use a function which locks the table to do the above operations, but it doesn't seem to work. There is something wrong with the function that I have written.
create type PointData as(PointId integer,PointType integer,CreateTime bigint);
create or replace function PP_PointBuildInfo() returns setof PointData as '
Declare
rec PointData;
begin
LOCK TABLE EBIHistory SHARE UPDATE EXCLUSIVE MODE;
for rec in SELECT pointid,pointtype,pointname,createtime
FROM pointtable p ,ebihistory e WHERE p.pointid = e.pointnumber AND e.flag=''0'' loop
return next rec;
end loop;
update ebihistory set flag=''1'';
COMMIT;
end;
' language 'plpgsql';
Could anyone help me out with this.
Regards,
Pradeep