Skip site navigation (1) Skip section navigation (2)

Help with concurrent insertions.

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2005-01-25 11:29:21
Subject: Re: programming language for postgresql
Previous:From: brewDate: 2005-01-25 07:33:25
Subject: Re: programming language for postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group