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, 6 Dec 2004, C G wrote:

> Is there a way to use locks within a trigger? My example below gives the
> error:

It's not the lock that's the problem I think. The begin work is failing
because you can't start a transaction inside the function. Were you trying
to release the lock at the commit in the function?

> 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;

> > Is there a way to use locks within a trigger? My example below gives the
> > error:
>
>It's not the lock that's the problem I think. The begin work is failing
>because you can't start a transaction inside the function. Were you trying
>to release the lock at the commit in the function?
Yes I was using the commit to release the lock.