Date: Thu, 15 Jan 2004 21:18:32 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Denis <sqllist@coralindia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Initially Deffered - FK
In-Reply-To: <006601c3dbea$02295800$0f32a8c0@denisnew>
Message-ID: <20040115211617.C53092@megazone.bigpanda.com>
References: <006601c3dbea$02295800$0f32a8c0@denisnew>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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.