To temporarily disable all triggers in a PostgreSQL session, use this:
|
1 |
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:
|
1 |
SET session_replication_role = DEFAULT; |
Disable a Single Trigger
To disable just a single trigger, use ALTER TABLE:
|
1 |
ALTER TABLE mytable DISABLE TRIGGER mytrigger; |
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:
|
1 |
ALTER TABLE mytable DISABLE TRIGGER ALL; |
To re-enable:
|
1 |
ALTER TABLE mytable ENABLE TRIGGER ALL; |
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.
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;
SET session_replication_role = replica; locks the tables? what happens if have another sessions in active transaction? Or opening another transaction?