Re: BUG #5326: The 2nd update of a table which has foreign keys is blocked.

"Sadao Hiratsuka" <sh2(at)pop01(dot)odn(dot)ne(dot)jp> wrote:

> PostgreSQL version: 8.4.2
> The 2nd update of a table which has foreign keys is blocked.
>
> <test case 1>
> create table parent (k int primary key, d varchar(10));
> create table child (k int primary key, f int, d varchar(10),
> constraint child_fk1 foreign key (f) references parent (k));
>
> insert into parent values (1, 'a');
> insert into parent values (2, 'b');
>
> insert into child values (11, 1, 'aa');
> insert into child values (12, 2, 'bb');
>
> client1> begin;
> client1> update parent set d = 'a2' where k = 1;
>
> client2> begin;
> client2> update child set d = 'aa2' where k = 11; -- ok
> client2> update child set d = 'aa3' where k = 11; -- blocked

The limitation still exists even in HEAD.
(Sorry for the wrong report in another mail, Hiratsuka-san.)

The comment in AfterTriggerSaveEvent() in commands/trigger.c says we
cannot skip FK checks when we update the same tuple in one transaction.

/*
* Update on FK table
*
* 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.
*/
if (!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(oldtup->t_data)) &&
RI_FKey_keyequal_upd_fk(trigger, rel, oldtup, newtup))
{
continue; <== skip the FK check
}

But to be exact, the comment says we *can* still skip the checks
if we don't have any deferred FKs, right? If so, can we add
a "has_deferred_FKs()" check to the condition?

if ((!has_deferred_FKs(rel) ||
!TransactionIdIsCurrentTransactionId(...)) &&
RI_FKey_keyequal_upd_fk(...)

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center