pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInitially Deffered - FK
6+ messages / 3 participants
[nested] [flat]
* Initially Deffered - FK
@ 2004-01-16 04:32 denis@coralindia.com
0 siblings, 0 replies; 6+ messages in thread
From: denis@coralindia.com @ 2004-01-16 04:32 UTC (permalink / raw)
To: pgsql-sql
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 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');
I have tested the same in ORACLE, and it works fine (i.e. both table has 4
records).
It is BUG or !!!
Pl. help.
Thanx
Denis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Initially Deffered - FK
@ 2004-01-16 04:34 Denis <sqllist@coralindia.com>
0 siblings, 1 reply; 6+ messages in thread
From: Denis @ 2004-01-16 04:34 UTC (permalink / raw)
To: pgsql-sql
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 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');
I have tested the same in ORACLE, and it works fine (i.e. both table has 4
records).
It is BUG or !!!
Pl. help.
Thanx
Denis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Initially Deffered - FK
@ 2004-01-16 05:18 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Denis <sqllist@coralindia.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2004-01-16 05:18 UTC (permalink / raw)
To: Denis <sqllist@coralindia.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Initially Deffered - FK
@ 2004-01-16 05:31 denis@coralindia.com
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: denis@coralindia.com @ 2004-01-16 05:31 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Initially Deffered - FK
@ 2004-01-19 10:33 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: denis@coralindia.com
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2004-01-19 10:33 UTC (permalink / raw)
To: denis@coralindia.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Initially Deffered - FK
@ 2004-01-19 12:11 Denis <sqllist@coralindia.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Denis @ 2004-01-19 12:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-01-19 12:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-16 04:32 Initially Deffered - FK denis@coralindia.com
2004-01-16 04:34 Initially Deffered - FK Denis <sqllist@coralindia.com>
2004-01-16 05:18 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-16 05:31 ` denis@coralindia.com
2004-01-19 10:33 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-19 12:11 ` Denis <sqllist@coralindia.com>
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