Search Postgresql Archives

Re: transaction confusion

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



This function, when run in 2 separate sessions at the same time, causes a duplicate key error because popartid is a primary key. This makes sense to me. When the function is run in the second session it doesn't know about the transaction currently running in the first session. Therefore, when it does its delete, it obviously doesn't delete that data. When the first one finishes it puts the data into the table. When the second one finishes, it attempts to put its data into the table but finds records already there, which violate the primary key and cause an error.

CREATE OR REPLACE FUNCTION populaterescheduleparts()
  RETURNS void AS
$BODY$
begin
delete from reschedulepoparts;
 insert into reschedulepoparts(popartid,priority,rescqty)
SELECT a.popartid, a.priority, sum(b.rescqty)
   FROM reschedulepoparts_2 a
   JOIN reschedulepoparts_1 b ON a.popartid = b.popartid AND a.priority = b.priority
group by a.popartid, a.priority;
return;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

The next one is a bit longer. I would expect that if it was run in 2 separate sessions that 1) it would generate the same error as the first one generated, because it also populates the same table and 2) that there would be twice the number of records in the stat_allocated_components table. However, no matter how many tests I run, this function always exits with the correct number of rows in the table and without error.


CREATE OR REPLACE FUNCTION populate_allocated_components()
  RETURNS void AS
$BODY$

declare
	statrec stat_allocated_components;
	crs refcursor;
	statpos stat_allocated_components;
	v_partid int;
	v_lfbused int8;
	v_lfused int8;
	v_lused int8;
	v_polf int8;
	v_pol int8;
	v_polfb int8;
	v_availableLF int8;
	v_availableL int8;
	v_availableLFB int8;
	v_balancel int8;
	v_balancelf int8;
	leftovers int8;
	futurel int8;
	futurelf int8;
	futurelfb int8;
	beforeupdate int8;
	v_firstneedl date;
	v_firstneedlf date;
	sumqty int8;
	rowresc record;
	leadfound bool;
	tqty int8;
Begin
update systemsettings set lastranallocated = now();

delete from stat_allocated_components;
delete from reschedulepoparts;

insert into stat_allocated_components(partid,quantity,assembliesbatchid,assemblyname,
					popartid,duedate,stock,leadfree,l,lf,lfb,lbp,leadstateid)
 SELECT a.partid, a.quantity, c.assembliesbatchid, d.assemblyname,0,
        CASE
            WHEN (c.entrydate + '49 days'::interval) < c.lastmodified
		  THEN c.lastmodified
            ELSE c.entrydate + '49 days'::interval
        END::timestamp with time zone AS duedate,
	case when d.leadfree
		then coalesce(g.totallf,0)
		else coalesce(g.totallead,0)
	end AS stock,
	 d.leadfree,coalesce(g.l,0),coalesce(g.lf,0),coalesce(g.lfb,0),coalesce(g.lbp,0),
	 case when d.leadfree
	 	then 1
		else 2
	 end
FROM  assemblies d  JOIN assembliesbatch c
 ON d.assemblyid = c.assemblyid
   JOIN allocatedassemblies a  ON a.assembliesbatchid = c.assembliesbatchid
 left Join stockperownerandleadstate g on g.partid=a.partid and g.ownerid=1
  WHERE c.assembliesbatchstatusid in (1,2,4,7) and a.quantity<0;



insert into stat_allocated_components(partid,quantity,assembliesbatchid,assemblyname,popartid,duedate,l,lf,lfb,lbp,leadstateid,postatusid)
 SELECT a.partid,  a.quantity, a.popartid AS assembliesbatchid,
	a.poref AS assemblyname, a.popartid AS popartid,
	a.expecteddate AS duedate,
	coalesce(g.l,0),coalesce(g.lf,0),coalesce(g.lfb,0),coalesce(g.lbp,0),a.leadstateid,postatusid
   FROM expectedpodelivery a
    Left join stockperownerandleadstate g on g.partid=a.partid and g.ownerid=1
;


v_partid=-1;
for statrec in select * from stat_allocated_components order by partid,duedate,assembliesbatchid
LOOP
	if v_partid<>statrec.partid then
		v_partid=statrec.partid;
		v_lfbused:=0;
		v_lfused:=0;
		v_lused:=0;
		v_polf:=0;
		v_pol:=0;
		v_polfb:=0;
		v_balancel:=0;
		v_balancelf:=0;
		v_firstneedl:=null;
		v_firstneedlf:=null;
		tqty:=0;
	end if;
	if statrec.quantity<0 then	
v_balancel:=statrec.stock -(v_lused+v_lfbused)+v_pol + v_polfb+case when not statrec.leadfree then statrec.quantity else 0 end; v_balancelf:=statrec.stock -(v_lfused+v_lfbused)+v_polf + v_polfb+case when statrec.leadfree then statrec.quantity else 0 end;
		if v_balancel<0 and v_firstneedl is null then
			v_firstneedl:=statrec.duedate;
		end if;
		if v_balancelf<0 and v_firstneedlf is null then
			v_firstneedlf:=statrec.duedate;
		end if;
update stat_allocated_components set previouscommitmentlf=v_lfused+v_lfbused,previouscommitmentl=v_lused+v_lfbused,previouspol=v_pol,previouspolf=v_polf,previouspolfb=v_polfb, balance=case when statrec.leadfree then v_balancelf else v_balancel end, instock=case when statrec.leadfree then v_balancelf else v_balancel end>0 where id=statrec.id;
		v_availableLF:=statrec.lf+v_polf-v_lfused;
		v_availableL:=statrec.l+statrec.lbp+v_pol-v_lused;
		v_availableLFB:=statrec.lfb+v_polfb-v_lfbused;

v_lfbused:=v_lfbused+lesserof(abs(statrec.quantity) - lesserof(case when statrec.leadfree then greaterof(v_availablelf,0) else greaterof(v_availablel,0) end,abs(statrec.quantity)),greaterof(v_availablelfb,0));
		if statrec.leadfree then
v_lfused:=v_lfused+lesserof(abs(statrec.quantity) - lesserof(abs(statrec.quantity) - lesserof(greaterof(v_availablelf,0),abs(statrec.quantity)),greaterof(v_availablelfb,0)),greaterof(v_availablelf,0));
			leftovers:= abs(statrec.quantity)-
							(lesserof(abs(statrec.quantity) -
									lesserof(abs(statrec.quantity) -
										lesserof(greaterof(v_availablelf,0),abs(statrec.quantity)),
									greaterof(v_availablelfb,0)),
								greaterof(v_availablelf,0)) +
								lesserof(abs(statrec.quantity) -
									lesserof(greaterof(v_availablelf,0),abs(statrec.quantity)),
								greaterof(v_availablelfb,0)));

		else
v_lused:=v_lused+lesserof(abs(statrec.quantity) - lesserof(abs(statrec.quantity) - lesserof(greaterof(v_availablel,0),abs(statrec.quantity)),greaterof(v_availablelfb,0)),greaterof(v_availablel,0));
			leftovers:= abs(statrec.quantity)-
							(lesserof(abs(statrec.quantity) -
									lesserof(abs(statrec.quantity) -
										lesserof(greaterof(v_availablel,0),abs(statrec.quantity)),
									greaterof(v_availablelfb,0)),
								greaterof(v_availablel,0)) +
								lesserof(abs(statrec.quantity) -
									lesserof(greaterof(v_availablel,0),abs(statrec.quantity)),
								greaterof(v_availablelfb,0)));
		end if;
		if leftovers>0 then
			futurelfb:=0;
			futurelf:=0;
			futurel:=0;
			if crs is not null then
				close crs;
			end if;
open crs for select * from stat_allocated_components where quantity>0 and partid=statrec.partid and (duedate>statrec.duedate or (duedate=statrec.duedate and assembliesbatchid>statrec.assembliesbatchid)) order by duedate,assembliesbatchid;
			fetch crs into statpos;
			While found LOOP
				if statpos.leadstateid =1 and statrec.leadfree then
					futurelf:=futurelf+statpos.quantity;
					beforeupdate:=v_lfused;
					v_lfused:=v_lfused + lesserof(leftovers,futurelf+v_polf+statrec.lf-v_lfused);
					leftovers:=leftovers-lesserof(leftovers,futurelf+v_polf+statrec.lf-beforeupdate);
				elsif statpos.leadstateid = 2 and not statrec.leadfree then
					futurel:=futurel+statpos.quantity;
					beforeupdate:=v_lused;
					v_lused:=v_lused + lesserof(leftovers,futurel+v_pol+statrec.l-v_lused);
					leftovers:=leftovers-lesserof(leftovers,futurel+v_pol+statrec.l-beforeupdate);
				elsif statpos.leadstateid in (3,4) then
					futurelfb:=futurelfb+statpos.quantity;
					beforeupdate:=v_lfbused;
					v_lfbused:=v_lfbused + lesserof(leftovers,futurelfb+v_polfb+statrec.lfb-v_lfbused);
					leftovers:=leftovers-lesserof(leftovers,futurelfb+v_polfb+statrec.lfb-beforeupdate);
				end if;
				if leftovers=0 then
					exit;
				end if;
				fetch crs into statpos;	
			end LOOP;
			if leftovers<>0 then
				if statrec.leadfree then
					v_lfused:=v_lfused+leftovers;
				else
					v_lused:=v_lused+leftovers;
				end if;
			end if;
		end if;

	else
		sumqty:=0;		
		if statrec.leadstateid = 2 then
			v_pol:=v_pol+statrec.quantity;
			if v_balancel<0 then
insert into reschedulepoparts(popartid,priority,rescqty) values(statrec.assembliesbatchid,v_firstneedl,lesserof(-v_balancel,statrec.quantity));
				if lesserof(-v_balancel,statrec.quantity)=-v_balancel then
					v_firstneedl:=null;
				else
for rowresc in select duedate,leadfree,balance from stat_allocated_components where not leadfree and duedate>=v_firstneedl and balance<0 and duedate<=statrec.duedate and partid=v_partid order by duedate,assembliesbatchid Loop
						sumqty:=-rowresc.balance;
						if sumqty>statrec.quantity then
							v_firstneedl:=rowresc.duedate;
							exit;
						end if;
					End Loop;
				end if;
				v_balancel:=v_balancel+statrec.quantity;
			end if;
		elsif statrec.leadstateid=1 then
			v_polf:=v_polf+statrec.quantity;
			if v_balancelf<0 then
insert into reschedulepoparts(popartid,priority,rescqty) values(statrec.assembliesbatchid,v_firstneedlf,lesserof(-v_balancelf,statrec.quantity));
				if lesserof(-v_balancelf,statrec.quantity)=-v_balancelf then
					v_firstneedlf:=null;
				else
for rowresc in select duedate,leadfree,balance from stat_allocated_components where leadfree and duedate>=v_firstneedlf and balance<0 and duedate<=statrec.duedate and partid=v_partid order by duedate,assembliesbatchid Loop
						sumqty:=-rowresc.balance;
						if sumqty>statrec.quantity then
							v_firstneedlf:=rowresc.duedate;
							exit;
						end if;
					End Loop;

				end if;
				v_balancelf:=v_balancelf+statrec.quantity;
			end if;
		elsif statrec.leadstateid in (3,4) then
			v_polfb:=v_polfb+statrec.quantity;	
			if v_balancelf<0 or v_balancel<0 then
insert into reschedulepoparts(popartid,priority,rescqty) values(statrec.assembliesbatchid,lesserof(v_firstneedlf,v_firstneedl),lesserof(-(lesserof(v_balancelf,v_balancel)),statrec.quantity)); if lesserof(-(lesserof(v_balancelf,v_balancel)),statrec.quantity)=-(lesserof(v_balancelf,v_balancel)) then
					v_firstneedlf:=null;
					v_firstneedl:=null;
				else
					leadfound:=null;
for rowresc in select duedate,leadfree,balance from stat_allocated_components where duedate>=v_firstneedlf and balance<0 and duedate<=statrec.duedate and partid=v_partid order by duedate,assembliesbatchid Loop
						sumqty:=-rowresc.balance;
						if leadfound is null then
							if sumqty>statrec.quantity then
								if rowresc.leadfree then
									v_firstneedlf:=rowresc.duedate;
									v_firstneedl:=null;
									leadfound:=false;
								else
									v_firstneedl:=rowresc.duedate;
									v_firstneedlf:=null;
									leadfound:=true;
								end if;
							end if;
						else
							if leadfound and rowresc.leadfree then
								v_firstneedlf:=rowresc.duedate;
								exit;
							elsif not leadfound and not rowresc.leadfree then
								v_firstneedl:=rowresc.duedate;
								exit;
							end if;
						end if;
					End Loop;					
				end if;
				v_balancel:=v_balancel+statrec.quantity;
				v_balancelf:=v_balancelf+statrec.quantity;
			end if;
		end if;
	end if;
End Loop;
return;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


Martijn van Oosterhout wrote:
On Sun, Sep 17, 2006 at 10:32:12AM +0200, Sim Zacks wrote:
I have a function that deletes all the values in a table and then inserts the data again. If this function is run in 2 sessions at the same time then it populates it twice, giving me a unique value error, because one of the fields is supposed to be unique.

<snip>

Does this make any sense?

Not overly much to me anyway. Could you provide some cut-and-paste
output from a psql session so we can see the actual commands you're
typing. It's possible the actual deletes and inserts are coded in a way
to cause a problem...

Hope this helps,


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux