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

Version: postgresql 9.0.1
Step to reproduce: use following code. It creates two tables (parent
"xtest" and inherited "xtest_inh"), creates trigger function, creates
BEFORE INSERT/UPDATE/DELETE trigger.

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

-- 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=20
    raise exception 'aaaaaaaaaaaaaaaaaaaaa!';
END; $$ language plpgsql;

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

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

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

**********************************

Expected result: exception will be raised before deletion of rows is
done.

Real result: no exception occurs. One of rows is really deleted.
The trigger is ignored.

Comments:=20
1. You can uncomment INSERT statement, and try again: exception
will be thrown. BEFORE INSERT works, BEFORE delete - no.
2. If i create trigger FOR EACH STATEMENT, it will work ok for insert,
update and delete.
3. AFTER DELETE statement-level trigger also does not work at all.

bug?

Konstantin Nikiforov <helllamer@gmail.com> writes:
> Expected result: exception will be raised before deletion of rows is
> done.

> Real result: no exception occurs. One of rows is really deleted.
> The trigger is ignored.

I believe this is intentional, though not too well documented.  If we
fired statement triggers on child tables, then we'd have to fire all
such triggers, potentially on hundreds of child tables.  In particular
this would result in a problem for constraint exclusion: discarding
child tables that couldn't be referenced by the query action would
result in a visible change in behavior.  So the designed behavior is
that only the named target table has its statement triggers fired.

> 1. You can uncomment INSERT statement, and try again: exception
> will be thrown. BEFORE INSERT works, BEFORE delete - no.

The reason the INSERT case works is you're naming the child table
as target.

> 2. If i create trigger FOR EACH STATEMENT, it will work ok for insert,
> update and delete.

You mean FOR EACH ROW, no?

            regards, tom lane

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 =3D 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.

Ok, thanks, roger you.
But one question still cause my misunderstanding:
> > 2. If i create trigger FOR EACH STATEMENT, it will work ok for
> > insert, update and delete.
>=20
> You mean FOR EACH ROW, no?

Yes, confused. FOR EACH ROW.

"FOR EACH ROW"-trigger successfully fires in inherited
table "xtest_inh", then we deleteting row from PARENT table "xtest".
=20
This behaviour conflicts with:

> So the designed behavior is
> that only the named target table has its statement triggers fired.

Konstantin Nikiforov <helllamer@gmail.com> writes:
> 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.

I think you fundamentally misunderstand how triggers on inherited tables
work.  A row-level trigger is fired for events on rows *in its table*.

            regards, tom lane