Temporarily Disable PostgreSQL Triggers

Temporarily Disable PostgreSQL Triggers
  • 4.00 / 5 5
1 vote, 4.00 avg. rating (83% score)

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

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:

Disable a Single Trigger

To disable just a single trigger, use ALTER TABLE:

The difference to the previous method is that ALTER TABLE will globally disable the trigger, affecting all database sessions, not just the current one.

To disable all triggers for one table:

To re-enable:

 

2 thoughts on “Temporarily Disable PostgreSQL Triggers”

  1. I wish it were possible to disable a particular trigger inside a session, without locking the table. ALTER TABLE mytable DISABLE TRIGGER mytrigger; will lock the table.

    1. If you disable a trigger inside a transaction, you indeed will lock the table until you either rollback or commit.

      You can disable a particular trigger for one transaction only by doing something like this:

      BEGIN;
      – disable the trigger:
      ALTER TABLE test DISABLE TRIGGER mytrigger;
      – the table will be locked at this point with an AccessExclusiveLock

      – perform your non-triggering operations here:
      INSERT INTO test …
      UPDATE test …
      DELETE FROM test …

      – re-enable and commit, releasing the table lock
      ALTER TABLE test ENABLE TRIGGER mytrigger;
      COMMIT;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

freeway-stephenville