plpgsql update bug?

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