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

Fri, 25 May 2001 00:11:57 -0400
Vinod Kurup <vkurup@massmed.org>
plpgsql update bug?
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 been deleted 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 old
 values?
 If so, we don't need to check, anything that succeeded 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).


Fri, 25 May 2001 11:47:38 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: plpgsql update bug?