PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE DISABLE

4 minute read

背景

Oracle 临时关闭或启用触发器的语法如下

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4001.htm

ALTER TRIGGER [ schema. ]trigger    
  { ENABLE    
  | DISABLE    
  | RENAME TO new_name    
  | COMPILE [ DEBUG ]    
      [ compiler_parameters_clause    
        [ compiler_parameters_clause ] ... ]    
      [ REUSE SETTINGS ]    
  } ;    

PostgreSQL 触发器开关语法更丰富一些

PostgreSQL中对应的语法在alter table中，因为触发器与表相关，这样设计语法也符合逻辑的。

https://www.postgresql.org/docs/devel/static/sql-altertable.html

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]    
    action [, ... ]    
    
    DISABLE TRIGGER [ trigger_name | ALL | USER ]    
    ENABLE TRIGGER [ trigger_name | ALL | USER ]    
    
    ENABLE REPLICA TRIGGER trigger_name    
    ENABLE ALWAYS TRIGGER trigger_name    

细节（注意触发器被触发还受制于session_replication_role参数与replica

always的配置。）

These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed.

The trigger firing mechanism is also affected by the configuration variable session_replication_role. Simply enabled triggers (the default) will fire when the replication role is “origin” (the default) or “local”. Triggers configured as ENABLE REPLICA will only fire if the session is in “replica” mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication role.

The effect of this mechanism is that in the default configuration, triggers do not fire on replicas. This is useful because if a trigger is used on the origin to propagate data between tables, then the replication system will also replicate the propagated data, and the trigger should not fire a second time on the replica, because that would lead to duplication. However, if a trigger is used for another purpose such as creating external alerts, then it might be appropriate to set it to ENABLE ALWAYS so that it is also fired on replicas.

This command acquires a SHARE ROW EXCLUSIVE lock.

trigger_name

Name of a single trigger to disable or enable.

ALL

Disable or enable all triggers belonging to the table. (This requires superuser privilege if any of the triggers are internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.)

USER

Disable or enable all triggers belonging to the table except for internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.

(内部用于foreign key, unique, 排他等约束的触发器除外)

关闭或开启指定或所有表，或者某个用户，某个schema下的所有表的触发器。

使用函数，参考：

https://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresql

For example if you have tables in a particular namespace it could be:

create or replace function disable_triggers(a boolean, nsp character varying) returns void as
$$
declare 
act character varying;
r record;
begin
    if(a is true) then
        act = 'disable';
    else
        act = 'enable';
    end if;

    for r in select c.relname from pg_namespace n
        join pg_class c on c.relnamespace = n.oid and c.relhastriggers = true
        where n.nspname = nsp
    loop
        execute format('alter table %I %s trigger all', r.relname, act); 
    end loop;
end;
$$
language plpgsql;
If you want to disable all triggers with certain trigger function it could be:

create or replace function disable_trigger_func(a boolean, f character varying) returns void as
$$
declare 
act character varying;
r record;
begin
    if(a is true) then
        act = 'disable';
    else
        act = 'enable';
    end if;

    for r in select c.relname from pg_proc p 
        join pg_trigger t on t.tgfoid = p.oid
        join pg_class c on c.oid = t.tgrelid
        where p.proname = f
    loop
        execute format('alter table %I %s trigger all', r.relname, act); 
    end loop;
end;
$$
language plpgsql;

also dosent work for me in Postgres 9.1. i use the two function described by bartolo-otrit with some modification. I modified the first function to make it work for me because the namespace or the schema must be present to identify the table correctly. The new code is :

CREATE OR REPLACE FUNCTION disable_triggers(a boolean, nsp character varying)
  RETURNS void AS
$BODY$
declare 
act character varying;
r record;
begin
    if(a is true) then
        act = 'disable';
    else
        act = 'enable';
    end if;

    for r in select c.relname from pg_namespace n
        join pg_class c on c.relnamespace = n.oid and c.relhastriggers = true
        where n.nspname = nsp
    loop
        execute format('alter table %I.%I %s trigger all', nsp,r.relname, act); 
    end loop;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION disable_triggers(boolean, character varying)
  OWNER TO postgres;
then i simply do a select query for every schema :

SELECT disable_triggers(true,'public');
SELECT disable_triggers(true,'Adempiere');