BUG #15106: The AFTER trigger is created separately on view, and the DML operation can not trigger the trigger

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 691525127(at)qq(dot)com
Subject: BUG #15106: The AFTER trigger is created separately on view, and the DML operation can not trigger the trigger
Date: 2018-03-12 05:51:51
Message-ID: 152083391168.1215.16892140713507052796@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15106
Logged by: pu qun
Email address: 691525127(at)qq(dot)com
PostgreSQL version: 10.0
Operating system: Red Hat Enterprise Linux Server release 6.0
Description:

When the AFTER trigger is created separately on a view, the DML operation
can not trigger this trigger.

The instruction of PostgreSQL's manual for triggers is as follows:
(https://www.postgresql.org/docs/10/static/sql-createtrigger.html):
The following table summarizes which types of triggers may be used on
tables, views, and foreign tables:

When Event Row-level
Statement-level

BEFORE INSERT/UPDATE/DELETE Tables and foreign tables
Tables, views, and foreign tables
TRUNCATE —
Tables
AFTER INSERT/UPDATE/DELETE Tables and foreign tables
Tables, views, and foreign tables
TRUNCATE —
Tables
INSTEAD OF INSERT/UPDATE/DELETE Views

TRUNCATE —

According to the instructions, you can see that when the type of trigger
is'AFTER', the Statement-level trigger can be used on a view.
That is to say, the syntax level AFTER trigger is created on the view, and
when the event is INSERT/UPDATE/DELETE, it can trigger the trigger.
However, experiments prove that only creating a Statement-level AFTER
trigger, the operation of INSERT/UPDATE/DELETE will not trigger the trigger.

1. create a trigger function:
postgres=# create or replace function debug() returns trigger as
postgres-# $$
postgres$#postgres$# declare
postgres$# begin
postgres$# raise notice '%', TG_NAME;
postgres$# return new;
postgres$# end;
postgres$#postgres$# $$
postgres-#language plpgsql;
CREATE FUNCTION

2. create a test table:
postgres=# create table digoal (id int);
CREATE TABLE

3. create view:
postgres=# create view v_digoal as select * from digoal;
CREATE VIEW

4. create a Statement-level AFTER trigger:
postgres=# create trigger tg03 after insert on v_digoal for each statement
execute procedure debug();
CREATE TRIGGER

5. Do DML operation on view. it is found that the DML operation does not
trigger the trigger.
postgres=# insert into v_digoal values (2);
INSERT 0 1

If the 'INSTEAD OF' type trigger is created,then do the DML operation will
trigger the AFTER Statement-level trigger which is created before.
1. create a Row-levell INSTEAD OF trigger:
postgres=# create trigger tg04 instead of insert on v_digoal for each row
EXECUTE PROCEDURE debug();
CREATE TRIGGER

2. Do the DML operation on the view again, it is found that the DML
operation not only triggers the newly created INSTEAD OF trigger, but also
triggers the above - mentioned AFTER trigger.
postgres=# insert into v_digoal values (2);
NOTICE: tg04
NOTICE: tg03
INSERT 0 1

It can be seen from the above example, the DML operation cannot trigger the
trigger when the Statement-level AFTER trigger is created on view alone.
But when create INSTEAD OF trigger at the same time, the DML operation can
trigger the AFTER trigger.
I want to know that this is correct?
Why didn't the mmanual mention this point?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-03-12 10:25:53 BUG #15107: No good plans when row-level security is enabled
Previous Message Alvaro Herrera 2018-03-10 02:24:18 Re: Unrecognized exception condition "deprecated_feature"