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

Konstantin wrote:
> * Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> [Thu, 12 Feb
> 2009 13:54:11 +0200]:
>> 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.
>
> To tell the truth I expected another behavior.
> Consider example below. The difference with the first one is that there
> are no attempts to modify foreign key at all, but result is the same.
> ...
> Are you sure parent tuple should be lock in shared mode if
> a) foreign key is not modified explicitly at all
> b) new value of foreign key is the same as old (not changed).

Yeah, you're right, we do have that optimization. However, it doesn't
kick in when you update a tuple that you've already updated in the same
transaction. The reason is explained by this comment in trigger.c:

>  * There is one exception when updating FK tables: if the
>  * updated row was inserted by our own transaction and the
>  * FK is deferred, we still need to fire the trigger. This
>  * is because our UPDATE will invalidate the INSERT so the
>  * end-of-transaction INSERT RI trigger will not do
>  * anything, so we have to do the check for the UPDATE
>  * anyway.

So this is an implementation artifact, due to the way foreign keys are
implemented in PostgreSQL.

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