如何暂时禁用 PostgreSQL 中的触发器？

How do I temporarily disable triggers in PostgreSQL?

提问by David Schmitt

I'm bulk loading data and can re-calculate all trigger modifications much more cheaply after the fact than on a row-by-row basis.

我正在批量加载数据，并且可以在事后比逐行更便宜地重新计算所有触发器修改。

How can I temporarily disable all triggers in PostgreSQL?

如何暂时禁用 PostgreSQL 中的所有触发器？

采纳答案by David Schmitt

PostgreSQL knows the ALTER TABLE tblname DISABLE TRIGGER USERcommand, which seems to do what I need. See ALTER TABLE.

PostgreSQL 知道ALTER TABLE tblname DISABLE TRIGGER USER命令，它似乎可以满足我的需要。请参阅更改表。

回答by zyzof

Alternatively, if you are wanting to disable all triggers, not just those on the USER table, you can use:

或者，如果您想禁用所有触发器，而不仅仅是 USER 表上的触发器，您可以使用：

SET session_replication_role = replica;

This disables triggers for the current session.

这将禁用当前会话的触发器。

To re-enable for the same session:

要为同一会话重新启用：

SET session_replication_role = DEFAULT;

Source: http://koo.fi/blog/2013/01/08/disable-postgresql-triggers-temporarily/

资料来源：http: //koo.fi/blog/2013/01/08/disable-postgresql-triggers-temporously/

回答by Mise

For disable trigger

用于禁用触发器

ALTER TABLE table_name DISABLE TRIGGER trigger_name

For enable trigger

对于启用触发器

ALTER TABLE table_name ENABLE TRIGGER trigger_name

回答by Neil McGuigan

You can also disable triggers in pgAdmin (III):

您还可以在 pgAdmin (III) 中禁用触发器：

1. Find your table
2. Expand the +
3. Find your trigger in Triggers
4. Right-click, uncheck "Trigger Enabled?"

1. 找到你的桌子
2. 展开 +
3. 在触发器中找到您的触发器
4. 右键单击，取消选中“触发器已启用？”

回答by bartolo-otrit

SET session_replication_role = replica; 

It doesn't work with PostgreSQL 9.4 on my Linux machine if i change a table through table editor in pgAdmin and works if i change table through ordinary query. Manual changes in pg_trigger table also don't work without server restart but dynamic query like on postgresql.nabble.com ENABLE / DISABLE ALL TRIGGERS IN DATABASEworks. It could be useful when you need some tuning.

如果我通过 pgAdmin 中的表编辑器更改表，则它不适用于 Linux 机器上的 PostgreSQL 9.4，如果我通过普通查询更改表，它也可以工作。pg_trigger 表中的手动更改在没有服务器重启的情况下也不起作用，但是像postgresql.nabble.com上的动态查询启用/禁用数据库中的所有触发器工作。当您需要进行一些调整时，它可能很有用。

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;

PostgreSQL documentation for system catalogs

系统目录的PostgreSQL 文档

---

---

There are another control options of trigger firing process:

触发器触发过程还有另一个控制选项：

ALTER TABLE ... ENABLE REPLICA TRIGGER ... - trigger will fire in replica mode only.

ALTER TABLE ... ENABLE REPLICA TRIGGER ... - 触发器仅在副本模式下触发。

ALTER TABLE ... ENABLE ALWAYS TRIGGER ... - trigger will fire always (obviously)

ALTER TABLE ... ENABLE ALWAYS TRIGGER ... - 触发器将始终触发（显然）

回答by Samih Chouhen

SET session_replication_role = replica;  

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 :

在 Postgres 9.1 中也对我有用。我使用了 bartolo-otrit 描述的两个函数并进行了一些修改。我修改了第一个函数以使其对我有用，因为命名空间或架构必须存在才能正确识别表。新代码是：

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');