Re: deadlock with truncate and foreing keys

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Alexey Nalbat" <nalbat(at)price(dot)ru> writes:
>> create table t1 ( id integer primary key, name text );
>> create table t2 ( id integer references t1 );
>> /* 1 */ truncate t2;
>> /* 2 */ update t1 set name='foo' where id=1;

> I think what's going on here is that in transaction 1 the TRUNCATE locks t2
> and then the INSERT tries to take a lock on a record in t1 to enforce the
> referential integrity.

I'm not sure whether the OP recognizes that this is a critical point,
but that UPDATE isn't changing t1.id and therefore there is no need
for it to look into t2 at all. The current code in fact does not look
into t2, much less take any row-level lock there; but nonetheless it
transiently takes AccessShareLock on t2. I'm thinking that might be
unnecessary ...

regards, tom lane