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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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