From: | Vinod Kurup <vkurup(at)massmed(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | plpgsql update bug? |
Date: | 2001-05-25 04:11:57 |
Message-ID: | 20010525001156.C1952@cartman.vkurup.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I think I've come across a bug in plpgsql. It happens in the following
situation:
I have 2 tables, one with a foreign key to the other.
Inside a plpgsql function, I do:
update row in table2
delete that row in table2
delete the referenced row in table1
And I get a foreign key constraint error. I apologize if that's not clear,
but hopefully the test case is more explanatory...
-- create --
create table foo (id integer primary key);
create table bar (id integer references foo);
insert into foo (id) values (1);
insert into bar (id) values (1);
create function f_1 ()
returns integer as '
begin
--any update statement causes problems
update bar set id=1 where id=1;
delete from bar where id = 1;
delete from foo where id = 1;
return 0;
end;' language 'plpgsql';
drop function f_2 ();
create function f_2 ()
returns integer as '
begin
-- no update statement
delete from bar where id = 1;
delete from foo where id = 1;
return 0;
end;' language 'plpgsql';
--Tests:
-- Tests attempt to delete a row from bar & foo
-- Thus the result of select count(*) from foo should be 0
--test1: Test plpgsql with an update before a delete -> fails
select f_1();
select count(*) from foo;
ERROR: <unnamed> referential integrity violation - key referenced from bar not found in foo
count
-------
1
--test2: Test plpgsql with just a delete -> succeeds
-- wrap in a transaction so I can rollback & do test3
begin transaction;
select f_2();
select count(*) from foo;
rollback;
count
-------
0
ROLLBACK
--test3: Test direct sql with update before a delete in transaction -> succeeds
begin transaction;
update bar set id=1 where id=1;
delete from bar where id = 1;
delete from foo where id = 1;
select count(*) from foo;
end transaction;
UPDATE 1
DELETE 1
DELETE 1
count
-------
0
COMMIT
It seems like function f_1 should succeed, but it doesn't...
Vinod
--
_____________________________
Vinod Kurup, MD
email: vkurup(at)massmed(dot)org
phone: 617.277.2012
cell: 617.359.5990
http://www.kurup.com
aim: vvkurup
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-05-25 05:00:42 | Re: GiST index on data types that require compression |
Previous Message | Lincoln Yeoh | 2001-05-25 02:26:51 | RE: Plans for solving the VACUUM problem |