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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15106: The AFTER trigger is created separately on view,and the DML operation can not trigger the trigger
Дата
Msg-id 152083391168.1215.16892140713507052796@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #15106: The AFTER trigger is created separately on view, and the DML operation can not trigger the trigger
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      15106
Logged by:          pu qun
Email address:      691525127@qq.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?


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Unrecognized exception condition "deprecated_feature"
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15107: No good plans when row-level security is enabled