如何在PostgreSQL中临时禁用触发器？

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

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/

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?"

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)

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

For disable trigger

ALTER TABLE table_name DISABLE TRIGGER trigger_name

For enable trigger

ALTER TABLE table_name ENABLE TRIGGER trigger_name

A really elegant way to handle this is to create a role that handles database population and set replication for that role:

ALTER ROLE role_name SET session_replication_role = 'replica';

That way you can use that role for populating data and not have to worry about disabling and renabling triggers etc.