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

User · Answer

You can also disable triggers in pgAdmin  III     Find your table Expand the   Find your trigger in Triggers Right-click  uncheck  Trigger Enabled

User · Answer

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

User · Answer

For disable trigger  ALTER TABLE table name DISABLE TRIGGER trigger name   For enable trigger  ALTER TABLE table name ENABLE TRIGGER trigger name

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] How do I temporarily disable triggers in PostgreSQL?

Examples related to postgresql

Examples related to triggers

Examples related to bulkinsert