Thread: 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:
> 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
>