Re: Initially Deffered - FK - Mailing list pgsql-sql

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@megazone.bigpanda.com>
To: "Denis" <sqllist@coralindia.com>
Cc: <pgsql-sql@postgresql.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.