Disable Triggers

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