How to disable PostgreSQL triggers in one transaction only?

Question

I need to temporary disable one PostgreSQL trigger in a transaction, but without hardlocking table. Does someone know if it is possible?

Something like this without locking table and disabling trigger outside of this transaction.

BEGIN TRANSACTION;

  ALTER TABLE foo DISABLE TRIGGER bar;

  -- DO SOME UPDATES ON foo
  UPDATE foo set field = 'value' where field = 'test';

  ALTER TABLE foo ENABLE TRIGGER bar;

COMMIT;

Answer 1

To temporarily disable all triggers in a PostgreSQL session, use this:

SET session_replication_role = replica;

That disables all triggers for the current database session only. Useful for bulk operations, but remember to be careful to keep your database consistent.

To re-enable:

SET session_replication_role = DEFAULT;

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

Answer 2

You can disable all triggers in this table. It should look like this:

ALTER TABLE tblname DISABLE TRIGGER USER;
Your SQL;
ALTER TABLE tblname ENABLE TRIGGER USER;

For disabling a single trigger use this:

ALTER TABLE tblname DISABLE TRIGGER trigger_name;
Your SQL;
ALTER TABLE tblname ENABLE TRIGGER trigger_name;

You can read more about ALTER TABLE in documentation.