Re: Bug: table inheritance. Trigger before DELETE for each statement is ignored

From: Konstantin Nikiforov <helllamer(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug: table inheritance. Trigger before DELETE for each statement is ignored
Date: 2010-12-03 08:16:12
Message-ID: 20101203111612.3304674e@avers
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Also another bug and usecase connected with subj.
It creates parent table "xtest", inherited table "xtest_inh" with
some data, and a trigger BEFORE INSERT/UPDATE/DELETE on table "xtest".
After, it deletes one row.

**********************************
CREATE TABLE xtest (id serial, data varchar, primary key(id));
CREATE TABLE xtest_inh (check (id > 0), primary key(id))
INHERITS (xtest);

-- insert some data to inherited table "xtest_inh"
INSERT INTO xtest_inh(data) values ('ddd'), ('lol'), ('olo');

-- this function just raises an exception every time
CREATE FUNCTION just_raise_exception_tg() returns trigger as $$
BEGIN
raise exception 'aaaaaaaaaaaaaaaaaaaaa!';
END; $$ language plpgsql;

-- adding STATEMENT-level trigger to inherited table "xtest_inh"
CREATE TRIGGER just_raise_exception_tg
BEFORE INSERT OR UPDATE OF data OR DELETE ON xtest
FOR EACH ROW execute procedure just_raise_exception_tg(2);

-- do some operations, that should cause to trigger the table
--INSERT into xtest(data) values ('omg');
DELETE from xtest where id = 2;

drop table xtest cascade;
drop function just_raise_exception_tg() cascade;
****************************************

Expected result:
Trigger should fire the exception before delete.

Real result:
Row successfully deleted. The trigger is ignored.

Comments:

1. Trigger fires ok when INSERT to table "xtest", but not fires for
DELETE. You can uncomment the line with INSERT (see above) and check.

2. Trigger fires ok when created with "FOR EACH STATEMENT" clause.
Trigger not fires when created with "FOR EACH ROW" clause.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Балукова Анна 2010-12-03 10:43:59
Previous Message Konstantin Nikiforov 2010-12-03 07:35:47 Re: Bug: table inheritance. Trigger before DELETE for each statement is ignored