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

Search Discussions

  • Michael Fuhr at Dec 7, 2004 at 5:21 am

    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?
  • Colin Gillespie at Dec 7, 2004 at 10:12 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 6, '04 at 3:43p
activeDec 7, '04 at 10:12a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Colin Gillespie: 2 posts Michael Fuhr: 1 post

People

Translate

site design / logo © 2022 Grokbase