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: [EMAIL PROTECTED]
phone: 617.277.2012
cell: 617.359.5990
http://www.kurup.com
aim: vvkurup
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
