From: denis@coralindia.com
To: pgsql-sql@postgresql.org
Subject: Re: Initially Deffered - FK
Date: Fri, 16 Jan 2004 11:01:13 +0530
Message-ID: <00ad01c3dbf1$f596ae00$0f32a8c0@denisnew> (raw)
References: <006601c3dbea$02295800$0f32a8c0@denisnew>
<20040115211617.C53092@megazone.bigpanda.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: denis@coralindia.com
Subject: Re: Initially Deffered - FK
In-Reply-To: <00ad01c3dbf1$f596ae00$0f32a8c0@denisnew>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox