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

referential constraint bug

From: Eric Lu <yclu(at)ecompliance(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: referential constraint bug
Date: 2002-03-07 00:49:34
Message-ID: 002b01c1c572$05ebd610$0c28a8c0@ecompliance.net (view raw or flat)
Thread:
Lists: pgsql-bugs
To whom it may concern:

 I found a constraint bug on PostgreSQL lastest version (7.2).

Here is how I got the bug.
------------------------------------------------------------------------------------------------
testdb=>
testdb=> create table mytest(
testdb-> id int primary key,
testdb-> masterid int constraint fk_mytest_constr1 REFERENCES mytest(id) ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
testdb-> );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'mytest_pkey' for table 'mytest'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
testdb=> begin;
BEGIN
testdb=> insert into mytest(id,masterid) values(1,null);
INSERT 447153 1
testdb=> insert into mytest(id,masterid) values(2,5);
INSERT 447154 1
testdb=> insert into mytest(id,masterid) values(3,2);
INSERT 447155 1
testdb=> update mytest set masterid=1 where id=2;
UPDATE 1
testdb=> end;
ERROR:  fk_mytest_constr1 referential integrity violation - key referenced from mytest not found in mytest
testdb=>
------------------------------------------------------------------------------------------------

Eric

PS. 1.There won't be such an error if I do another insert statement which doesn't violate the reference constraint
    before ending the transaction.(eg.insert into mytest(id,masterid) values(4,3);)
  2.It happens while I was using ver7.1.3 and I do an upgrade and run the regression test(everything is fine).
  3.It's on a pentium 75(run as 120MHZ) with 40MB RAM, Redhat 6.1

Responses

pgsql-bugs by date

Next:From: Laurent FAILLIEDate: 2002-03-07 10:47:08
Subject: Re: Bug #608: cache lookup failed
Previous:From: Tom LaneDate: 2002-03-06 18:35:49
Subject: Re: Bug #608: cache lookup failed

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