Thread: Initially Deffered - FK

Initially Deffered - FK

From
"Denis"
Date:
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 intransaction:
begin;delete from contact where id=1;insert into contact values (1, 'Denis');delete from address where id=1;    /* this
isnot required.. but myapp.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 4records).
It is BUG or !!!
Pl. help.
Thanx
Denis






Re: Initially Deffered - FK

From
Stephan Szabo
Date:
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.


Re: Initially Deffered - FK

From
denis@coralindia.com
Date:
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.




Re: Initially Deffered - FK

From
Stephan Szabo
Date:
On Fri, 16 Jan 2004 denis@coralindia.com wrote:

> 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.)

If you use the definition we're using now, then no, the above is not true.
The row would still exist just before commit because the on delete action
has not happened.  The row would not exist just after commit because the
on delete action now has. Whether this definition is correct per spec is
hard to say, but there've been arguments on the subject in the past that
have generally ended without a firm understanding of the specs intention.




Re: Initially Deffered - FK

From
"Denis"
Date:
Hi Stephan,

Thanks for your reply.

Will have to work on its workaround...

Denis


----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: <denis@coralindia.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, January 19, 2004 4:03 PM
Subject: Re: [SQL] Initially Deffered - FK


>
> On Fri, 16 Jan 2004 denis@coralindia.com wrote:
>
> > 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.)
>
> If you use the definition we're using now, then no, the above is not true.
> The row would still exist just before commit because the on delete action
> has not happened.  The row would not exist just after commit because the
> on delete action now has. Whether this definition is correct per spec is
> hard to say, but there've been arguments on the subject in the past that
> have generally ended without a firm understanding of the specs intention.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings