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))
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_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 = 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:
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?

Search Discussions

  • Tom Lane at Dec 3, 2010 at 3:52 am

    Konstantin Nikiforov 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
  • Konstantin Nikiforov at Dec 3, 2010 at 7:36 am
    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.
    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".

    This behaviour conflicts with:
    So the designed behavior is
    that only the named target table has its statement triggers fired.
  • Konstantin Nikiforov at Dec 3, 2010 at 8:16 am
    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.
  • Tom Lane at Dec 3, 2010 at 3:58 pm

    Konstantin Nikiforov 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedDec 2, '10 at 8:24p
activeDec 3, '10 at 3:58p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Konstantin Nikiforov: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2021 Grokbase