Skip site navigation (1) Skip section navigation (2)

BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE
Date: 2011-07-19 18:31:39
Message-ID: 201107191831.p6JIVdxu055846@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      6123
Logged by:          Kevin Grittner
Email address:      Kevin(dot)Grittner(at)wicourts(dot)gov
PostgreSQL version: HEAD and 9.0.4
Operating system:   Linux (probably doesn't matter)
Description:        DELETE fails if before trigger causes another trigger to
UPDATE
Details: 

create table a (aid int not null primary key,
                bcnt int not null default 0);
create table b (bid int not null primary key,
                aid int not null);
 
create function a_del_func()
  returns trigger language plpgsql as
  $$begin delete from b where aid = old.aid;
          return old; end;$$;
create trigger a_del_trig before delete On a
  for each row execute procedure a_del_func();
 
create function b_ins_func()
  returns trigger language plpgsql as
  $$begin update a set bcnt = bcnt + 1
            where aid = new.aid; return new; end;$$;
create trigger b_ins_trig after insert on b
  for each row execute procedure b_ins_func();
 
create function b_del_func()
  returns trigger language plpgsql as
  $$begin update a set bcnt = bcnt - 1
            where aid = old.aid; return old; end;$$;
create trigger b_del_trig after delete on b
  for each row execute procedure b_del_func();
 
insert into a values (1, 0);
insert into b values (10, 1);
select * from a; select * from b;
delete from a where aid = 1;
select * from a; select * from b;
 
I expect the DELETE from a to delete related b record(s) and then succeed. 
Instead the UPDATE in b_del_trig causes delete of a to silently fail.
 
-Kevin

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-07-19 21:30:47
Subject: Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE
Previous:From: Dave PageDate: 2011-07-19 16:42:09
Subject: Re: BUG #6122: Installation fails: "database cluster initialisation failed"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group