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