How do I temporarily disable triggers in PostgreSQL?

Question

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?

score 253 · Accepted Answer · answered Sep 10 '13 at 02:49

253

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/

answered Sep 10 '13 at 02:49

zyzof

3,415
1
25
21

4

Awesome. Made my mass-deletion go from 30 minutes to <1 second :) – Dan Lenski Sep 06 '14 at 01:30
15

It is also handy that this command doesn't disable constraint triggers – bartolo-otrit Jan 30 '15 at 11:18
2

I've spent the last half hour looking in vain for a way to bypass "violates foreign key constraints" error in my test environment, and this is it exactly! – Alkanshel Nov 05 '15 at 02:16
4

One caveat: according to [runtime config docs](https://www.postgresql.org/docs/10/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE) and the [ALTER TABLE docs](https://www.postgresql.org/docs/10/static/sql-altertable.html) this will work with normal triggers but not those set with `ENABLE REPLICA` or `ENABLE ALWAYS`. – beldaz Jul 07 '18 at 21:04
3

I'm on `10.4` and it seems to ignore this above statement. – Stephane Oct 25 '18 at 13:07
2

Be careful it also disables checks of foreign key constraints. – Pifagorych Sep 25 '19 at 11:30
doesnt work in v12 as superuser – Wayneio Jan 19 '22 at 16:41
1

@zyzof, I suggest add a warning about disabling also foreign keys. – Emilio Platzer May 29 '22 at 15:19
Be aware that this method requires certain privileges – Jarl Dec 06 '22 at 08:21

David Schmitt · Answer 2 · 2010-10-15T12:47:18.793

163

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

edited Oct 15 '10 at 12:47

answered Oct 15 '10 at 12:40

David Schmitt

58,259
26
121
165

And then how do you "re-calculate all trigger modifications"? – Wojtek Kruszewski Mar 20 '14 at 11:11
26

Careful with concurrent load: `ALTER TABLE ... DISABLE TRIGGER USER` requires an exclusive lock on the table. – Erwin Brandstetter May 27 '14 at 22:14
4

@WojtekKruszewski, I think that David meant that He can re-calculate the changes manually that would have done by trigger, by using some prior knowledge (for example, if the trigger will make the same change in every row, which can be more efficiently handled by a single UPDATE). I don't think that He meant that You can do this in every situation. – Rauni Lillemets Aug 05 '14 at 10:29
1

@zyzof's solution is better for disabling all triggers. – uthomas Mar 01 '17 at 07:17
1

But this has the BIG problem that you have to use the database exclusively, because the triggers will also be disabled in other sessions! – Daniel Jun 22 '23 at 06:18
@Daniel Really? Should be fine if you disable and re-enable them in a transaction – Bergi Aug 30 '23 at 01:23

score 103 · Answer 3 · answered Oct 18 '17 at 13:58

103

For disable trigger

ALTER TABLE table_name DISABLE TRIGGER trigger_name

For enable trigger

ALTER TABLE table_name ENABLE TRIGGER trigger_name

answered Oct 18 '17 at 13:58

Mise

3,267
1
22
22

21

You may also use "all" for this: `ALTER TABLE table_name DISABLE TRIGGER all` – DenisNovac Jun 15 '20 at 04:03

bartolo-otrit · Answer 4 · 2015-06-26T17:58:17.983

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)

score 8 · Answer 5 · answered Nov 15 '13 at 22:40

8

You can also disable triggers in pgAdmin (III):

Find your table
Expand the +
Find your trigger in Triggers
Right-click, uncheck "Trigger Enabled?"

answered Nov 15 '13 at 22:40

Neil McGuigan

46,580
12
123
152

score 8 · Answer 6 · answered Feb 03 '17 at 15:44

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

score 5 · Answer 7 · answered May 13 '22 at 08:37

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.

How do I temporarily disable triggers in PostgreSQL?

7 Answers7

Linked