Skip site navigation (1) Skip section navigation (2)

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

From: Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: "Sadao Hiratsuka" <sh2(at)pop01(dot)odn(dot)ne(dot)jp>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5326: The 2nd update of a table which has foreign keys is blocked.
Date: 2010-02-16 06:51:55
Message-ID: 20100216155155.9D8F.52131E4D@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-bugs
"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



In response to

Responses

pgsql-bugs by date

Next:From: Maxim BogukDate: 2010-02-16 11:10:45
Subject: BUG #5328: GIN index with fastupdates=on provide wrong result on bitmap scan
Previous:From: Fujii MasaoDate: 2010-02-16 05:19:15
Subject: Re: Re: [BUGS] BUG #4566: pg_stop_backup() reports incorrect STOP WAL LOCATION

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group