From: Denis <sqllist@coralindia.com>
To: pgsql-sql@postgresql.org
Subject: Initially Deffered - FK
Date: Fri, 16 Jan 2004 10:04:18 +0530
Message-ID: <006601c3dbea$02295800$0f32a8c0@denisnew> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sqllist@coralindia.com
Subject: Re: Initially Deffered - FK
In-Reply-To: <006601c3dbea$02295800$0f32a8c0@denisnew>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox