From: | "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Help with concurrent insertions. |
Date: | 2005-01-25 10:08:00 |
Message-ID: | 77ED2BF75D59D1439F90412CC5B10974182F5044@ie10-sahara.hiso.honeywell.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2005-01-25 11:29:21 | Re: programming language for postgresql |
Previous Message | brew | 2005-01-25 07:33:25 | Re: programming language for postgresql |