Today I Learned

hashrocket A Hashrocket project

Temporarily Disable Triggers

In general, you are always going to want your triggers to fire. That's why they are there. Though special circumstances may arise where you need to temporarily disable them. In postgres, you can use

> set session_replication_role = 'replica';

By changing the replication role from origin to replica you are essentially disabling all non-replica triggers across the database (for that session). When you are done, you can simply set the replication role back so that normal trigger behavior can resume

> set session_replication_role = 'origin';

A more direct and fine-grained approach to disabling triggers is to use an alter table command that targets a specific trigger.

h/t Jack Christensen

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.