How to disable PostgreSQL triggers in one transaction only?

database postgresql triggers postgresql-9.5
gori picture gori · Jun 9, 2016 · Viewed 9.1k times · Source

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

Dave picture Dave · Mar 8, 2018

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/

Related questions

Using pg_notify in PostgreSQL trigger function

I am attempting to issue a notification from a PostgreSQL trigger function. I can successfully use the NOTIFY command, but I am not having any luck with pg_notify. Even though I receive a notification when I invoke the pg_…

Read More
What are PostgreSQL RULEs good for?

Question I often see it stated that rules should be avoided and triggers used instead. I can see the danger in the rule system, but certainly there are valid uses for rules, right? What are they? I'm asking this out …

Read More
When we need return value from trigger procedure?

Documentation about trigger procedures (https://www.postgresql.org/docs/9.6/static/plpgsql-trigger.html) says: "A trigger function must return either NULL or a record/row". Example CREATE TRIGGER my_trigger AFTER INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE some_…

Read More