Andres Freund wrote: > On 2013-01-10 18:00:40 -0300, Alvaro Herrera wrote: > > Here's version 28 of this patch. The only substantive change here from > > v26 is that I've made GetTupleForTrigger() use either LockTupleExclusive > > or LockTupleNoKeyExclusive, depending on whether the key columns are > > being modified by the update. This needs to go through EvalPlanQual, so > > that function is now getting the lock mode as a parameter instead of > > hardcoded LockTupleExclusive. (All other users of GetTupleForTrigger > > still use LockTupleExclusive, so there's no change for anybody other > > than FOR EACH ROW BEFORE UPDATE triggers). > > Is that enough in case of a originally non-key update in read committed > mode that turns into a key update due to a concurrent key update?
Hm, let me try to work through your example. You say that a transaction T1 does a non-key update, and is working through the BEFORE UPDATE trigger; then transaction T2 does a key update and changes the key underneath T1? So at that point T1 becomes a key update, because it's now using the original key values which are no longer the key? I don't think this can really happen, because T2 (which is requesting TupleLockExclusive) would block on the lock that the trigger is grabbing (TupleLockNoKeyExclusive) on the tuple. So T2 would sleep until T1 is committed. Now, maybe you meant that the BEFORE UPDATE trigger changes the key value but the user-supplied UPDATE query does not. So the trigger turns the no-key update into a key update. What would happen here is that GetTupleForTrigger would acquire TupleLockNoKeyExclusive on the tuple, and later heap_update would acquire TupleLockExclusive. So there is lock escalation happening. This could cause a deadlock against someone else grabbing a TupleLockKeyShare on the tuple. I think the answer is "don't do that", i.e. don't update the key columns in a BEFORE UPDATE trigger. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
