plpgsql update bug?

Started by Vinod Kurupover 24 years ago2 messages

vkurup@massmed.org

over 24 years ago

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@massmed.org
phone: 617.277.2012
cell: 617.359.5990
http://www.kurup.com
aim: vvkurup