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
foowhere 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 foocount
------- 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 1count
------- 0
COMMIT
It seems like function f_1 should succeed, but it doesn't...
Vinod
--
_____________________________
Vinod Kurup, MD
email: vkurup@massmed.org
phone: 617.277.2012
cell: 617.359.5990
http://www.kurup.com
aim: vvkurup
