Horton

Thread

Commits

  1. Doc: note that statement-level view triggers require an INSTEAD OF trigger.

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

    The Post Office <noreply@postgresql.org> — 2018-03-12T05:51:51Z

    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?

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-03-12T20:27:33Z

    =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> 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.

When there's no INSTEAD OF trigger, the query is rewritten into an insert
on the view's base table (assuming the view is simple enough to be
auto-updatable), and we fire the base table's statement triggers, not the
view's.  I'm pretty sure this is intentional, though I couldn't find it
mentioned in the manual either.  Firing both sets of statement triggers
would be confusing, and not firing the base table's triggers would
perhaps miss processing that needs to happen.

			regards, tom lane

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

    Thomas Munro <thomas.munro@enterprisedb.com> — 2018-03-12T22:33:32Z

    On Tue, Mar 13, 2018 at 9:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>> 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.
>
> When there's no INSTEAD OF trigger, the query is rewritten into an insert
> on the view's base table (assuming the view is simple enough to be
> auto-updatable), and we fire the base table's statement triggers, not the
> view's.  I'm pretty sure this is intentional, though I couldn't find it
> mentioned in the manual either.  Firing both sets of statement triggers
> would be confusing, and not firing the base table's triggers would
> perhaps miss processing that needs to happen.

We also discussed this here:

https://www.postgresql.org/message-id/flat/CACjxUsOrn%2B3FgaLzskuLB3hASW6iTUd6f40gq_q80a9NHXk92A%40mail.gmail.com

We resolved the main issue in that thread but we didn't do anything
about this side issue.  You and Kevin both suggested that perhaps we
shouldn't allow you to create such unfireable triggers, or perhaps we
should raise a warning, or at least document the (IMHO) confusing
behaviour.

-- 
Thomas Munro
http://www.enterprisedb.com

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-03-18T19:14:30Z

    Thomas Munro <thomas.munro@enterprisedb.com> writes:
> On Tue, Mar 13, 2018 at 9:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> When there's no INSTEAD OF trigger, the query is rewritten into an insert
>> on the view's base table (assuming the view is simple enough to be
>> auto-updatable), and we fire the base table's statement triggers, not the
>> view's.  I'm pretty sure this is intentional, though I couldn't find it
>> mentioned in the manual either.  Firing both sets of statement triggers
>> would be confusing, and not firing the base table's triggers would
>> perhaps miss processing that needs to happen.

> We also discussed this here:

> https://www.postgresql.org/message-id/flat/CACjxUsOrn%2B3FgaLzskuLB3hASW6iTUd6f40gq_q80a9NHXk92A%40mail.gmail.com

> We resolved the main issue in that thread but we didn't do anything
> about this side issue.  You and Kevin both suggested that perhaps we
> shouldn't allow you to create such unfireable triggers, or perhaps we
> should raise a warning, or at least document the (IMHO) confusing
> behaviour.

A simple approach to raising an error or warning would create hazards
for pg_dump, ie it'd need to make sure the INSTEAD OF trigger was restored
first.  We could get around that no doubt, but I don't think it's worth
the work, at least not unless we get a lot more complaints.  For the
moment a documentation improvement seems sufficient, so I've pushed one.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a4678320471380e5159a8d6e89466d74d6ee1739

			regards, tom lane