Initially Deferred - FK

From: "Denis" <sqllist(at)coralindia(dot)com>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: Initially Deferred - FK
Date: 2004-01-15 13:04:39
Message-ID: 00a601c3db68$2845ca50$cb32a8c0@RKCOMPAQ
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

[reSending, ignore if it reached here ealier. Did sent in the morning, but
seems it never reached.]

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;
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

Browse pgsql-odbc by date

  From Date Subject
Next Message Philippe Lang 2004-01-15 16:13:28 Re: No Current Record
Previous Message Richard Huxton 2004-01-15 09:38:36 Re: PID for the conection