Hallo,
also hier einmal die beiden Funktionen in vollem Ausmaß. Beim gleichzeitigen Aufruf passieren immer wieder Deadlocks. Vielleicht gibt es auch eine einfachere Lösung diese zu verhindern. Bin für jeden Ratschlag dankbar.
Lg, Kicmad
Funktion 1:
CREATE OR REPLACE FUNCTION getbsddakeys(smallint, smallint, smallint)
RETURNS SETOF record AS
$BODY$
/**
* This function returns all DA Keys of registered DA Tabs which have to be refreshed and resets their refresh flag.
*
* @usage: SELECT * FROM getbsddakeys(1::int2, 0::int2, 3::int2) AS
* DEPT(busystatus int2,tabnumber int2, rownumber int2, columnnumber int2, terminalrolenumber int4, refresh int2,
* idcommunicationtype int8, localmodecode int4, trainmodecode int4, nrisdn char(20), idregistereddatabs int8);
* @param thislocation number of the calling location
* @param norefresh value of the refresh flag for tabs which need no update
* @param newtab value of the refresh flag for tabs after page switches
* @return: resultset of all selected DA Keys
*/
DECLARE
thislocation ALIAS FOR $1;
norefresh ALIAS FOR $2;
newtab ALIAS FOR $3;
regtabids int8[] := '{}'::int8[];
lastregtabid int8 := -1;
strSQLwithKeys text;
strSQLnoKeys text;
recAffected record;
BEGIN
strSQLwithKeys := 'SELECT bs.busystatus,tabs.tabnumber, keys.rownumber, keys.columnnumber, aur.terminalrolenumber,
tabs.refresh, keys.idcommunicationtype, aur.localmodecode, aur.trainmodecode, aur.nrisdn, tabs.idregistereddatabs
FROM
tableregistereddatabs tabs, tableactivedakeys keys, tablebusystatus bs, tableactiveuserroles aur
WHERE
tabs.refresh <> '|| norefresh ||' AND
tabs.locationnumber = '|| thislocation ||' AND
keys.tabnumber = tabs.tabnumber AND
keys.idactiveuserrole = tabs.idactiveuserrole AND
aur.idactiveuserrole = tabs.idactiveuserrole AND
keys.idbusystatus = bs.idbusystatus
ORDER BY tabs.idregistereddatabs
FOR UPDATE OF tabs;';
FOR recAffected IN EXECUTE strSQLwithKeys LOOP
IF lastregtabid <> recAffected.idregistereddatabs THEN
regtabids := regtabids || recAffected.idregistereddatabs;
lastregtabid := recAffected.idregistereddatabs;
END IF;
RETURN NEXT recAffected;
END LOOP;
strSQLnoKeys := 'SELECT -1::int2, tabs.tabnumber, -1::int2, -1::int2, aur.terminalrolenumber, tabs.refresh, -1::int8, -1::int4, -1::int4,
aur.nrisdn, tabs.idregistereddatabs FROM tableregistereddatabs tabs, tableactiveuserroles aur
WHERE
tabs.refresh = '|| newtab ||' AND aur.idactiveuserrole = tabs.idactiveuserrole AND tabs.locationnumber = '|| thislocation || '';
IF array_upper(regtabids, 1) IS NOT NULL THEN
strSQLnoKeys := strSQLnoKeys || ' AND tabs.idregistereddatabs NOT IN ('|| array_to_string(regtabids, ', ') ||')';
END IF;
strSQLnoKeys := strSQLnoKeys || ' FOR UPDATE OF tabs;';
FOR recAffected IN EXECUTE strSQLnoKeys LOOP
regtabids := regtabids || recAffected.idregistereddatabs;
RETURN NEXT recAffected;
END LOOP;
IF array_upper(regtabids, 1) IS NOT NULL THEN
EXECUTE 'UPDATE tableregistereddatabs SET refresh = '|| norefresh ||' WHERE idregistereddatabs IN ('|| array_to_string(regtabids, ', ') ||')';
END IF;
RETURN;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION getbsddakeys(smallint, smallint, smallint) OWNER TO postgres;
Funktion 2:
CREATE OR REPLACE FUNCTION storeinternalbusystatus(character varying, smallint, smallint, smallint)
RETURNS void AS
$BODY$
/**
* This function
* - sets busy status of physicalnumbers
* - sets refresh flag of registered DA tabs which are affected by busy status change
* - sets refresh flag of busy status interface registrations affected by busy status change
*
* @usage SELECT storeinternalbusystaus('2123385',1,1,1);
* @param physicalnumber varchar of physicalnumber where busystatus is to be set
* @param busystatus int2 value to be set - 0...free, 1...busy, 2...undefined
* @param refresh int2 value to be set at refresh values
* @param locationnumber int2 location which initiates this update
* @return void
*/
BEGIN
-- update refresh flag of registered DA tabs
UPDATE tableregistereddatabs SET refresh = $3
WHERE idregistereddatabs IN (
SELECT dat.idregistereddatabs
FROM (
-- get neighbours by physicalnumber of parent
SELECT bs.idbusystatus, bsn.physicalnumber, false as isparent
FROM tablebusystatus bs
INNER JOIN (
SELECT idbusystatus,idbusystatusneighbour
FROM tableactiveneighbourhoods
UNION
SELECT idbusystatusneighbour,idbusystatus
FROM tableactiveneighbourhoods
) nb USING (idbusystatus)
INNER JOIN tablebusystatus bsn ON bsn.idbusystatus = nb.idbusystatusneighbour
UNION
-- get parent by its own physicalnumber
SELECT idbusystatus, physicalnumber, true as isparent
FROM tablebusystatus
) bs
INNER JOIN tableactivedakeys da USING (idbusystatus)
INNER JOIN tableregistereddatabs dat USING(idactiveuserrole,tabnumber)
WHERE bs.physicalnumber = $1
)
AND refresh < 2;
-- update refresh flag of busy status interface registrations
UPDATE tablebsifregistrations SET refresh = $3
FROM (
-- get neighbours by physicalnumber of parent
SELECT bs.idbusystatus, bsn.physicalnumber, false as isparent
FROM tablebusystatus bs
INNER JOIN (
SELECT idbusystatus,idbusystatusneighbour
FROM tableactiveneighbourhoods
UNION
SELECT idbusystatusneighbour,idbusystatus
FROM tableactiveneighbourhoods
) nb USING (idbusystatus)
INNER JOIN tablebusystatus bsn ON bsn.idbusystatus = nb.idbusystatusneighbour
UNION
-- get parent by its own physicalnumber
SELECT idbusystatus, physicalnumber, true as isparent
FROM tablebusystatus
) bs
WHERE tablebsifregistrations.idbusystatus=bs.idbusystatus
AND bs.physicalnumber = $1;
-- update busy status of physicalnumber
UPDATE tablebusystatus
SET busystatus = $2, locationnumber = $4
FROM (
-- get neighbours by physicalnumber of parent
SELECT bs.idbusystatus, bsn.physicalnumber, false as isparent
FROM tablebusystatus bs
INNER JOIN (
SELECT idbusystatus,idbusystatusneighbour
FROM tableactiveneighbourhoods
UNION
SELECT idbusystatusneighbour,idbusystatus
FROM tableactiveneighbourhoods
) nb USING (idbusystatus)
INNER JOIN tablebusystatus bsn ON bsn.idbusystatus = nb.idbusystatusneighbour
UNION
-- get parent by its own physicalnumber
SELECT idbusystatus, physicalnumber, true as isparent
FROM tablebusystatus
) bs
WHERE bs.physicalnumber = $1 AND tablebusystatus.idbusystatus = bs.idbusystatus;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION storeinternalbusystatus(character varying, smallint, smallint, smallint) OWNER TO postgres;