Re: Initially Deffered - FK

Hi Stephan,

Thanks for your reply.

But, you will agree that result should be same JUST BEFORE and JUST AFTER
commit ( assuming no one is working on the database and i am the only user
connected.)

Till, the commit ( or end ) is issued, if you query ADDRESS, you will get 4
rows. This is expected result. But, just issue commit and see, the result
gets changed !!

Is this behaviour rectified / changed in later release of PG (say 7.3 or
7.4) ?

Any help will be appreciated.

Thanx

Denis

----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Denis" <sqllist(at)coralindia(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Sent: Friday, January 16, 2004 10:48 AM
Subject: Re: [SQL] Initially Deffered - FK

>
> On Fri, 16 Jan 2004, Denis wrote:
> > 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');
>
> Definitional difference. We currently treat a
> request to defer the constraint to mean defer
> referential actions as well, thus the inserted
> address is removed when the on delete cascade
> occurs after it at transaction end. Noone's
> been entirely sure whether this is correct
> or not per spec as I remember.