Lock inside trigger
Hello list,
I have pl/pgsql trigger that before insert a details table it must
update a column in other table, the trigger have this code:
CREATE OR REPLACE FUNCTION public.tg_ecproc_insert()
RETURNS trigger AS
' DECLARE
salproc numeric(12,2);
BEGIN
select coalesce(prvsalproc,0) into salproc from
prvdor where prvcode=new.ecp_provcode;
-- How do I prevent someone else to change
-- prvdor.prvsalproc during this transaction
if new.ecp_dc=''C'' then
new.ecp_saldo = salproc - new.ecp_valor;
else
new.ecp_saldo = salproc + new.ecp_valor;
end if;
update prvdor set prvsalproc = new.ecp_saldo
where prvcode=new.ecp_provcode ;
-- Here I should be able to unlock the row on
-- prvdor table
return new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
Does postgresql automatically handle the lock on tables updated from
within a trigger? or what's the must appropiate way to get this done?
Thanks in advance,
--
Josu� Maldonado
I'm not sure that postgres will do anything special (regarding locking) on
the other table that you're updating using a trigger.
Append the phrase "FOR UPDATE" in your select statement - this will give
you a row level lock on that table. After you issue the UPDATE statement
(later on) on that same table the lock will be released.
The change to your code should be:
select coalesce(prvsalproc,0) into salproc from
prvdor where prvcode=new.ecp_provcode
for update;
Hope that helps.
John Sidney-Woollett
Josu� Maldonado said:
Show quoted text
Hello list,
I have pl/pgsql trigger that before insert a details table it must
update a column in other table, the trigger have this code:CREATE OR REPLACE FUNCTION public.tg_ecproc_insert()
RETURNS trigger AS
' DECLARE
salproc numeric(12,2);
BEGIN
select coalesce(prvsalproc,0) into salproc from
prvdor where prvcode=new.ecp_provcode;
-- How do I prevent someone else to change
-- prvdor.prvsalproc during this transaction
if new.ecp_dc=''C'' then
new.ecp_saldo = salproc - new.ecp_valor;
else
new.ecp_saldo = salproc + new.ecp_valor;
end if;
update prvdor set prvsalproc = new.ecp_saldo
where prvcode=new.ecp_provcode ;
-- Here I should be able to unlock the row on
-- prvdor table
return new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;Does postgresql automatically handle the lock on tables updated from
within a trigger? or what's the must appropiate way to get this done?Thanks in advance,
--
Josu� Maldonado
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings