"Sadao Hiratsuka" <sh2@pop01.odn.ne.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