Re: Disable Triggers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> 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.

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
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkf9EUUACgkQvJuQZxSWSsgrQwCg7Q6ZBLBzzfy5fntxXPI17i8l
VTUAoNK++VH2lVj42tstfXM49P7NtCa+
=ex6Z
-----END PGP SIGNATURE-----