How to disable PostgreSQL triggers in one transaction only?

Question

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;

Jameson

Answer 1

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/

Aberrant

Will SET session_replication_role = replica; end up locking the table? – Sarabia

Answering my own question it would appear to be no – Sarabia

Answer 2

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.

Defalcate

Yes but I want it to be disabled only in one sql transaction and don't want to block table while triggers are disabled. DISABLE TRIGGER : hardlocks table when in transaction – Jameson

This doesn't answer gori 's question. He along with myself need to disable the trigger without locking the table. Say my trasnsaction takes 60 seconds and the trigger is disabled. The table would be locked and that is unacceptable in produciton. – Sarabia

Answer 3

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.

Jackboot

To disable trigger chaining you can look at the stack depth - check out pg_trigger_depth() function – Garment

I had to tweak this slightly: when (coalesce(current_setting('pokko.loading'::text, true), 'FALSE') <> 'TRUE'::text) – Nonpartisan

As of 9.2, Amit is right that pg_trigger_depth() would probably be a more native and proper solution. I haven't tested it, but I would trust that this would work just as well. – Jackboot

+1 this is pure genius and a great way to prevent infinite loops if you are using triggers to log changes to a table then using the change log for two-way sync between databases. For anyone else trying this, remember the variable name has to be dotted (e.g. foo.bar will work but foobar won't and Postgres will complain that it doesn't know the configuration variable...see docs). – Intercession

Yes, the variable name needs to have some delimited prefix first. I prefix all of mine with some_domain_name to distinguish them from system variables anyways. – Jackboot