Re: Disable Triggers - Mailing list pgsql-general

On Wednesday 09 April 2008 14:56, Greg Sabino Mullane wrote:
> > I see the following in the documentation for pg_trigger related
> > to tgenabled: "Controls in which session_replication_role modes the
> > trigger fires. O = trigger fires in "origin" and "local" modes,
> > D = trigger is disabled, R = trigger fires in "replica" mode, A =
> > trigger fires always."
> >
> > My question is: When tgenabled is set to "D", how does that setting
> > interact with session_replication_role and, is there a way to use
> > tgenabled with a setting of "D" to prevent a particular trigger
> > from firing. Using ALTER TABLE to disable the trigger won't work
> > because the whole table is locked during the transaction and I only
> > want the disabled trigger to apply to the current transaction in the
> > current session.
>
> If you simply want to ignore all triggers, just use a 'replica' role.
> When done, switch it back to 'origin' (or your default, which should
> be origin).
>
> If you want to fire only a single trigger, set it to 'always' mode and
> switch to 'replica'. If you want to fire all triggers *except* a
> certain trigger, set that trigger to replica mode and leave the
> session_replication_mode unchanged (default/origin).
>
> You should be using ALTER TABLE and not worry about changing tgenabled
> yourself, in case it wasn't obvious. You should be able to make permanent
> changes and then just use session_replication_role to control how it acts
> in a particular transaction.

Greg,

Thanks for your help on this. I'll try to work out something along these
lines. I'm inclined to update one of the system tables to accomplish this
because that's the way we did it in version 7.4.x. In that case, we were
setting reltriggers to 0 in pg_class to turn off all the triggers on a given
table, and, in fact, I was doing that at Tom's suggestion for solving the
problem in a post to the list long, long, ago, and far, far, away. Again,
thanks for taking the time to help :o]

>
> Here's a quick example:
>
> SET client_min_messages = 'ERROR';
> DROP SCHEMA IF EXISTS triggertest CASCADE;
> SET client_min_messages = 'NOTICE';
>
> CREATE SCHEMA triggertest;
>
> SET SEARCH_PATH = triggertest;
>
> CREATE TABLE foo(a int);
>
> INSERT INTO foo VALUES (1);
>
> CREATE FUNCTION trig1()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $_$
>  BEGIN
>  RAISE NOTICE 'I am trigger one';
>  RETURN NULL;
>  END;
> $_$;
>
> CREATE FUNCTION trig2()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $_$
>  BEGIN
>  RAISE NOTICE 'I am trigger two';
>  RETURN NULL;
>  END;
> $_$;
>
> CREATE FUNCTION trig3()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $_$
>  BEGIN
>  RAISE NOTICE 'I am trigger three';
>  RETURN NULL;
>  END;
> $_$;
>
> CREATE TRIGGER t1 AFTER UPDATE on foo
> FOR EACH ROW EXECUTE PROCEDURE trig1();
>
> CREATE TRIGGER t2 AFTER UPDATE on foo
> FOR EACH ROW EXECUTE PROCEDURE trig2();
>
> CREATE TRIGGER t3 AFTER UPDATE on foo
> FOR EACH ROW EXECUTE PROCEDURE trig3();
>
> UPDATE foo SET a=a; -- all three fire
>
> ALTER TABLE foo ENABLE ALWAYS TRIGGER t1;
>
> ALTER TABLE foo ENABLE REPLICA TRIGGER t2;
>
> UPDATE foo SET a=a; -- two does not fire
>
> SET session_replication_role TO 'replica';
>
> UPDATE foo SET a=a; -- three does not fire
>
> SET session_replication_role TO DEFAULT;
>
> UPDATE foo SET a=a; -- two does not fire
>
> The output of the above yields:
>
> CREATE TRIGGER
> psql:trig.example:53: NOTICE:  I am trigger one
> psql:trig.example:53: NOTICE:  I am trigger two
> psql:trig.example:53: NOTICE:  I am trigger three
> UPDATE 1
> ALTER TABLE
> ALTER TABLE
> psql:trig.example:59: NOTICE:  I am trigger one
> psql:trig.example:59: NOTICE:  I am trigger three
> UPDATE 1
> SET
> psql:trig.example:63: NOTICE:  I am trigger one
> psql:trig.example:63: NOTICE:  I am trigger two
> UPDATE 1
> SET
> psql:trig.example:67: NOTICE:  I am trigger one
> psql:trig.example:67: NOTICE:  I am trigger three
> UPDATE 1
>
>
> --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200804091452
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com