Re: transaction confusion - Mailing list pgsql-general

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,