Thread: Disable Triggers
Greetings:
I have a situation where an external process needs to disable the firing of
triggers on a table. I have two backend functions which handle this task:
disable_triggers ('table_name')
enable_triggers ('table_name')
These functions are called inside a transaction. The first, stores various
attributes of the triggers in a secondary table, then updates pg_trigger
setting tgenabled to 'D'. The second restores the value of tgenabled from the
secondary table and then deletes the records from the secondary table.
I can begin a transaction withing psql, issue: SELECT disable_triggers
('cust'), make an update to cust and the before and after triggers all fire
just like normal. I can do a \d cust inside the same transaction and the
triggers sections is listed with the heading: "Disabled Triggers". I can look
at the pg_trigger records associated with the cust table, and tgenabled is
set to "D", but the triggers still fire. session_replication_role is set to
"origin". I thought this was supposed to be fixed in later versions of
Postgres (I'm converting from 7.4.19 to 8.3.1), so apparently I'm missing
something.
Does anyone have any insight?
TIA
--
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I have a situation where an external process needs to disable the firing of > triggers on a table. ... > session_replication_role is set to "origin". I thought this was supposed to > be fixed in later versions of Postgres (I'm converting from 7.4.19 to 8.3.1), > so apparently I'm missing something. You want: SET session_replication_role to 'replica'; - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200804091058 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkf82cMACgkQvJuQZxSWSshqbwCfURuaWGtih7HEIrPs3lOCU+2V zN8An3eEH3G/2emX0pl2Z2NmszXB7kiN =cu+o -----END PGP SIGNATURE-----
On Wednesday 09 April 2008 11:00, Greg Sabino Mullane wrote: > > I have a situation where an external process needs to disable the firing > > of triggers on a table. > > ... > > > session_replication_role is set to "origin". I thought this was supposed > > to be fixed in later versions of Postgres (I'm converting from 7.4.19 to > > 8.3.1), so apparently I'm missing something. > > You want: SET session_replication_role to 'replica'; Thanks for the response Greg. Should the session_replication_role be restored to "origin", when the process is complete? > > -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200804091058 > 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
On Wednesday 09 April 2008 11:00, Greg Sabino Mullane wrote: > > I have a situation where an external process needs to disable the firing > > of triggers on a table. > > ... > > > session_replication_role is set to "origin". I thought this was supposed > > to be fixed in later versions of Postgres (I'm converting from 7.4.19 to > > 8.3.1), so apparently I'm missing something. > > You want: SET session_replication_role to 'replica'; > > -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200804091058 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 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. TIA -- 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
Terry Lee Tucker <terry@chosen-ones.org> writes:
> 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.
I'll bet you're missing a relcache flush operation. I don't think an
update on pg_trigger will cause that by itself.
regards, tom lane
On Wednesday 09 April 2008 13:12, Tom Lane wrote: > Terry Lee Tucker <terry@chosen-ones.org> writes: > > 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. > > I'll bet you're missing a relcache flush operation. I don't think an > update on pg_trigger will cause that by itself. > > regards, tom lane Thanks for the response Tom. I hate to be dense, but I really don't have a clue as to what you are saying. I can't find anything in the docs regarding "relcache flush". I have to get this issue resolved as our system uses a Perl process to keep certain columns in certain tables in sync across several databases, so, if you can point me in the right direction, that would be great. -- 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
-----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-----
Greg Sabino Mullane wrote: > -----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. The issue at hand (I work with the OP), is that our current application disables all triggers quite often. Enter Slony, we want to replicate. So, what we need to do is, disable ALL triggers EXCEPT slony triggers. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> You should be using ALTER TABLE and not worry about changing tgenabled
> yourself, in case it wasn't obvious.
Yeah. I had imagined Terry was hacking some backend code to do this,
in which case invoking CacheInvalidateRelcache directly might be
reasonable. But updating tgenabled directly from client code is Just A
Bad Idea.
regards, tom lane
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