Re: [HACKERS] deadlock with truncate and foreing keys

On Mon, 18 Feb 2008, Tom Lane wrote:

> Alexey Nalbat <nalbat(at)price(dot)ru> writes:
> > create table t1 ( id integer primary key, name text );
> > create table t2 ( id integer references t1 );
> > insert into t1 values ( 1 );
> > insert into t2 values ( 1 );
>
> > Then two concurrent transactions start.
>
> > /* 1 */ begin;
> > /* 1 */ truncate t2;
> > /* 2 */ begin;
> > /* 2 */ update t1 set name='foo' where id=1;
> > /* 1 */ insert into t2 values ( 1 );
>
> > Here we have deadlock.
>
> Hmm, this happens because RI_FKey_keyequal_upd_pk does
>
> fk_rel = heap_open(riinfo.fk_relid, AccessShareLock);
>
> but right offhand I see no reason for it to do so --- it doesn't
> *do* anything with fk_rel except close it again. Likewise
> RI_FKey_keyequal_upd_fk doesn't seem to really need to touch the
> pk_rel. Is there something I'm missing in that? Maybe this is
> a vestige of earlier coding that did need to touch both rels
> to perform the keysequal check?

Probably something like that - maybe ri_BuildQueryKeyFull might have
needed it open. Actually, I'm wondering if the ri_BuildQueryKeyFull call
is also unnecessary now - I don't think we ever use the qkey that comes
out of it unless I'm missing some code.