Skip site navigation (1) Skip section navigation (2)

Re: BUG #4648: needless deadlock on tables having foreign-key

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Konstantin <kostya2702(at)rambler(dot)ru>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #4648: needless deadlock on tables having foreign-key
Date: 2009-02-12 17:34:46
Message-ID: 49945DB6.3060809@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Konstantin wrote:
> * Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [Thu, 12 Feb 2009 10:54:34 -0500]:
>> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> > Hmm, the first UPDATE should've blocked already. It should've fired 
> a
>> RI
>> > trigger to lock the parent tuple in shared mode, but it looks like
>> > that's not happening for some reason.
>>
>> Read the special code in AfterTriggerSaveEvent. This behavior is
>> exactly what is expected --- since the referencing field didn't
>> change, only the second update attempt actually fires the trigger.
> 
> Why? The second update is identical to the first one.
> What is the difference? Such behavior looks very strange.

It's certainly not ideal. It's an implementation artifact of the way 
MVCC and RI triggers work. The purpose is to protect from this potential 
bug:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

-- This queues a trigger to check that there's a row in parent
-- with matching parent id. Its execution is deferred to end
-- of transaction.
INSERT INTO child (temp, parentid) VALUES (1, 1);

-- Update.the just-inserted row. Since we didn't change the foreign
-- key column, no RI trigger is queued.
UPDATE child SET temp = 1 WHERE pid = 1; -

-- This tries to run the trigger queued by the INSERT. But it's
-- not run because the row version doesn't exist anymore, because
-- it was later updated. If there wasn't a row in parent table with
-- id 1, we wouldn't throw an error like we should.
COMMIT

We're avoiding this scenario by always queuing the RI trigger, even if 
the key was not changed, if the updated tuple was inserted in the same 
transaction. That also applies to row versions that were not inserted, 
but are a result of an earlier update in the same transaction (UPDATE is 
internally very much like INSERT+DELETE)

Hmm, the comment specifically talks about deferrable RI checks. I wonder 
if we could skip that when there's no deferred triggers queued?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-bugs by date

Next:From: John R PierceDate: 2009-02-12 18:53:30
Subject: Re: BUG #4646: Default password is patently absurd
Previous:From: Tom LaneDate: 2009-02-12 17:04:32
Subject: Re: BUG #4647: Geometric functions reorder their parameters randomly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group