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,
