How do I temporarily disable triggers in PostgreSQ

问题:

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?

回答1:

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

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/

回答2:

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

回答3:

For disable trigger

ALTER TABLE table_name DISABLE TRIGGER trigger_name

For enable trigger

ALTER TABLE table_name ENABLE TRIGGER trigger_name

回答4:

You can also disable triggers in pgAdmin (III):

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

回答5:

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 DATABASE works. It could be useful when you need some tuning.

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

---

There are another control options of trigger firing process:

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

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

回答6:

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 :

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