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

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