Disabling triggers in stored procedures

Question

I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of states. However, there is a function that inserts a record in that table that fires that trigger (it starts in a different state).

My question now, is it OK to disable that trigger using ALTER TABLE in that function to get this working, or should I rethink my trigger design? Another work around would be to insert it in the "start" state and immediately UPDATE the record to make a state-step.

Answer 1

You can disable the trigger in the database function, but any actions on the table with the trigger that occur outside of that transaction (functions create implicit transactions) will be blocked until the function completes.

Simple test case:

-- In session 1
drop table if exists test;
create table test ( x int, y text );

create or replace function test_trg_f() returns trigger as $func$
begin
    perform null;
    return NEW;
end;
$func$ language plpgsql;

CREATE TRIGGER test_trg BEFORE INSERT ON public.test FOR EACH ROW EXECUTE PROCEDURE public.test_trg_f();

-- in session 2
DO $$
begin
    alter table test disable trigger test_trg;
    perform pg_sleep(10);
    alter table test enable trigger test_trg;
end;
$$;

-- In session 1, will be blocked until session 2 completes
INSERT INTO test (x,y) VALUES (1,1);