-----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@xxxxxxxxxxxx PGP Key: 0x14964AC8 200804091452 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkf9EUUACgkQvJuQZxSWSsgrQwCg7Q6ZBLBzzfy5fntxXPI17i8l VTUAoNK++VH2lVj42tstfXM49P7NtCa+ =ex6Z -----END PGP SIGNATURE-----
