Thread: triggers, transactions and locks

Dear All,

Is there a way to use locks within a trigger? My example below gives the error:
ERROR:  unexpected error -8 in EXECUTE of query "BEGIN"
CONTEXT:  PL/pgSQL function "insert_into_t1" line 6 at execute statement

Thanks

Colin


CREATE FUNCTION insert_into_t1() RETURNS trigger AS'
    DECLARE
        set_qry text;
    BEGIN

        set_qry:=''BEGIN WORK'';
        execute set_qry;
        LOCK t1;
        INSERT INTO t2 VALUES (11);
        COMMIT;
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER insert_into_t1 BEFORE INSERT
    ON t1 FOR EACH ROW EXECUTE
        PROCEDURE insert_into_t1();

On Mon, Dec 06, 2004 at 03:41:07PM -0000, Colin Gillespie wrote:

> Is there a way to use locks within a trigger? My example below gives the error:
> ERROR:  unexpected error -8 in EXECUTE of query "BEGIN"
> CONTEXT:  PL/pgSQL function "insert_into_t1" line 6 at execute statement

As Stephan Szabo already mentioned in response to your post in
pgsql-general, the problem isn't with the lock but rather with your
attempt to start and end a transaction within a function.

Can you tell us what you're trying to do?  Why do you want to lock
the table but not hold the lock until the outer transaction ends?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

>> Is there a way to use locks within a trigger? My example below gives
>> the error:
>> ERROR:  unexpected error -8 in EXECUTE of query "BEGIN"
>> CONTEXT:  PL/pgSQL function "insert_into_t1" line 6 at
>execute statement

>Can you tell us what you're trying to do?  Why do you want to
>lock the table but not hold the lock until the outer transaction ends?
Hi Michael,

In my trigger after an update, the trigger scans the table and creates a
row new row with one the counters going from i=i+1. However, I've found
that if two updates occur together then two identical rows can be
created. Hence, I thought of locking the table within the trigger.

I presume from your email that the best way would be to lock the table
from the beginning of the transaction?

Thanks

Colin

>
>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/
>