Thread: Initially Deffered - FK
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
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.
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.
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.
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