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

The following bug has been logged online:

Bug reference: 5326
Logged by: Sadao Hiratsuka
Email address: sh2(at)pop01(dot)odn(dot)ne(dot)jp
PostgreSQL version: 8.4.2
Operating system: Linux x86
Description: The 2nd update of a table which has foreign keys is
blocked.
Details:

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

Especially, a deadlock occurs by the following sequence.

<test case 2>
client1> begin;
client1> update parent set d = 'a2' where k = 1;

client2> begin;
client2> update parent set d = 'b2' where k = 2;
client2> update parent set d = 'a3' where k = 1; -- blocked

client1> update child set d = 'bb2' where k = 12; -- ok
client1> update child set d = 'bb3' where k = 12; -- deadlock

Is this PostgreSQL's bug? or right behavior?

Thanks,

"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

Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> 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(...)

No. That wouldn't fix this problem (since the OP hasn't got any
deferred triggers) and it would break the test we need to make
(since the deferred FK isn't necessarily *this* FK, and even if
it was, what you propose would prevent it from being checked).

If we knew that the just-outdated tuple had been created by an update
that didn't change the FK columns, then we could skip applying the
check at the new update. But we don't know that, and I don't see any
real easy way to shoehorn in the knowledge. We don't have any extra
per-tuple state here --- and per-tuple state wouldn't be good enough
anyway, if there are multiple FKs. Another possibility is to chain
back to the latest tuple version that existed prior to this transaction
and compare FK columns against that version ... except we have no good
way to do that either; the t_ctid links point the wrong way.

AFAICS there is no simple way to improve this. It's an optimization
that the first update didn't block. We can't easily extend that
optimization to the second update. Sorry.

regards, tom lane