CODEWITHSUNDEEP
databasepostgresqltriggerspostgresql-9.5
gori
gori

Reputation: 1258

How to disable PostgreSQL triggers in one transaction only?

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;

Upvotes: 30

Views: 25146

Answers (3)

Alexi Theodore
Alexi Theodore

Reputation: 1677

I had this exact same issue and figured out a clever and clean way to resolve it.

Firstly, you cannot disable a trigger within a trigger if that is the trigger that you are currently executing. That was my scenario - I was inserting into a table as a result of inserts to it - which would otherwise cause an infinite loop of triggers.

The way I resolved it was to add a local parameter variable into the mix that essentially acted as a global variable that would disable the trigger from further invocations when it was already being used.

To do so, add the following code to your trigger function, at the very beginning:

SET LOCAL your.variable_name to 'TRUE';

and then (assuming you're working with pg ≥ 9.6) you can just add the following line to your CREATE TRIGGER:

WHEN (current_setting('your.variable_name', 't') <> 'TRUE')

I haven't done any bench testing, but from my prior experiences I expect it to be very performant.

UPDATE:

I've been using this approach for several years now in a highly active data warehouse and now have it setup on all my triggers (there are probably 50 of them) by default. I've formalized the code as such:

CREATE OR REPLACE FUNCTION trigger_state(
    IN setting_name TEXT
) RETURNS BOOLEAN AS
$$
BEGIN

RETURN coalesce(NOT upper(current_setting(setting_name, TRUE)) = 'DISABLED',TRUE);

END
$$
LANGUAGE plpgsql
STABLE
;

and use it like this:

CREATE TRIGGER some_trigger
    BEFORE INSERT
    ON some_table
    FOR EACH ROW
    WHEN (
            trigger_state('some_domain_name.trigger_state.some_trigger')
        AND OLD.some_column IS DISTINCT FROM NEW.some_column
        )
    EXECUTE FUNCTION some_trg_funct()
;

To disable the trigger (for any reason) whether in code or command line, just:

SET "some_domain_name.trigger_state.some_trigger" = 'DISABLED';

This works in transactions, sessions, etc. as you'd expect with the SET syntax. It isn't subject to locks or anything like that and within the current session/scope, takes affect immediately.

NOTE: However, its affect is NOT predictable globally (especially in the short term). If you need to disable a trigger globally, then the best method of doing that is with the cannonical methods mentioned above.

Anyways, its been hugely instrumental for me. I've had nothing but success with it - at least as far as I've been able to tell many guzillion of txns later.

Upvotes: 11

Dave
Dave

Reputation: 4694

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/

Upvotes: 36

Walerian Sobczak
Walerian Sobczak

Reputation: 849

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.

Upvotes: 13

Related Questions