Message-ID: <006601c3dbea$02295800$0f32a8c0@denisnew>
From: "Denis" <sqllist@coralindia.com>
To: <pgsql-sql@postgresql.org>
Subject: Initially Deffered - FK
Date: Fri, 16 Jan 2004 10:04:18 +0530
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit


Hi all,

I am using :
PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)

 I am facing strange problem..

 I have created two tables:

 create table contact (id int constraint contact_pk primary key, name
text );
create table address (id int constraint address_fk references contact(id) on
delete cascade initially deferred,
                       city text,
                       pin text);

 Lets.. insert few data in it..

 insert into contact values (1, 'Denis');
insert into contact values (2, 'Anand');
insert into contact values (3, 'Debatosh');
insert into contact values (4, 'Pradeep');

insert into address values (1,'Howrah','711102');
insert into address values (2,'Kolkata','700001');
insert into address values (3,'Jadavpur','700005');
insert into address values (4,'Mumbai','400002');

Now, below gives me the correct result.

select * from contact; select * from address;

acedg=> select * from contact; select * from address;
 id |   name
----+----------
  1 | Denis
  2 | Anand
  3 | Debatosh
  4 | Pradeep
(4 rows)

  id |   city   |  pin
 ----+----------+--------
   1 | Howrah   | 711102
   2 | Kolkata  | 700001
   3 | Jadavpur | 700005
   4 | Mumbai   | 400002
 (4 rows)

 BUT, the problem starts when i issue the following set of DMLs in
 transaction:

 begin;
 delete from contact where id=1;
 insert into contact values (1, 'Denis');
 delete from address where id=1;    /* this is not required.. but my
 app.fires. Should not have any impact */
 insert into address values (1,'Howrah','711102');
 end;

 It gives me the result:

 acedg=> select * from contact; select * from address;
  id |   name
 ----+----------
   2 | Anand
   3 | Debatosh
   4 | Pradeep
   1 | Denis
 (4 rows)

  id |   city   |  pin
 ----+----------+--------
   2 | Kolkata  | 700001
   3 | Jadavpur | 700005
   4 | Mumbai   | 400002
 (3 rows)

 Where is my lastly inserted row ?? i.e.
 insert into address values (1,'Howrah','711102');

 I have tested the same in ORACLE, and it works fine (i.e. both table has 4
 records).

 It is BUG or !!!

 Pl. help.

 Thanx

 Denis