Обсуждение: BUG #15106: The AFTER trigger is created separately on view,and the DML operation can not trigger the trigger
BUG #15106: The AFTER trigger is created separately on view,and the DML operation can not trigger the trigger
От
PG Bug reporting form
Дата:
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?
=?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
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
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