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?
Show quoted text
CREATE FUNCTION insert_into_t1() RETURNS trigger AS'
DECLARE
set_qry text;
BEGINset_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.