Thread: 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

On Fri, 25 May 2001, Vinod Kurup wrote:

> 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...

Okay, I think I may understand why this occurs.  This is a
very similar problem to the defered constraints problem we
have.  It doesn't realize that the fk row isn't there anymore
and shouldn't be checked.

My guess is that these statements are all treated as part of 
a single statement when put inside the function which is why
they're treated differently than as separate statements in a
transaction.  

I'm not sure whether or not this is actually a triggered data change
violation (I don't have a draft of 99 to check right now) as it's
attempting to delete a row that was previously modified in the statement
(assuming that it's treated as a single statement of course).  I think the
triggered data change may only apply to updates though.

I think the following checks are needed (at least for the deferred case, 
and this case as well).  These checks only work for match full and
match unspecified, but we don't support match partial anyway:
On insert/update to fk check, can we see a row exist with the new values?If not, we don't need to check, it's already
beendeleted or updated again in which case we want the later trigger to act.
 
On delete/update from pk with no action, can we see a row with the oldvalues?If so, we don't need to check, anything
thatsucceeded before will succeed now.
 

I'm a bit uncertain on the deferred cases with action.  The spec is none
too clear about when the actions occur.  Although it appears to me
that it's at statement time, not check time since it mentions things
like "marked for deletion" which I believe is a statement level thing
(with said rows deleted at the end of the statement before integrity
checks are applied).