Re: triggers, transactions and locks - Mailing list pgsql-novice

From Colin Gillespie
Subject Re: triggers, transactions and locks
Date
Msg-id 20DA376D0C991745A4D5249F7BCD7A2521B4F3@largo.campus.ncl.ac.uk
Whole thread Raw
In response to triggers, transactions and locks  ("Colin Gillespie" <C.Gillespie@newcastle.ac.uk>)
List pgsql-novice
>> 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/
>

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: message type 0x44
Next
From: "Colin Gillespie"
Date:
Subject: Slow delete