Disabling triggers in a transaction

Jay Guerette <jayguerette@gmail.com> writes:

If I disable INSERT and UPDATE triggers inside a transaction; by
setting and resetting reltriggers in pg_class; am I correct in
thinking that this will disable triggers globally for that table for
the duration of that transaction?

Not if you never commit the pg_class row in that state.

regards, tom lane

This caught my eye the other day, but didn't take the time to examine it. I
find that I am now very interested in it. Could you please elaborate on your
method and how this works?

TIA

On Sunday 27 February 2005 05:37 pm, Jay Guerette saith:

Tom,

Do you feel this is a safe method for disabling triggers in the rare cases
where one finds that it is prudent to do that? Do you think that the column,
"reltriggers", is permanent fixture in pg_class? What is your advice on this?

TIA

On Monday 28 February 2005 03:22 pm, Tom Lane saith:

Terry Lee Tucker wrote:

Tom,

Do you feel this is a safe method for disabling triggers in the rare cases
where one finds that it is prudent to do that? Do you think that the column,
"reltriggers", is permanent fixture in pg_class? What is your advice on this?

I'd be quite interested in this as well. Can one depend on this column
in the future? Even if not, as long as one verifies it still exists, is
this a viable option for trigger control within a transaction?

(This guy Tucker comes up with some interesting stuff...) :)

--
Until later, Geoffrey

Geoffrey wrote:

Terry Lee Tucker wrote:

Tom,

Do you feel this is a safe method for disabling triggers in the rare cases
where one finds that it is prudent to do that? Do you think that the column,
"reltriggers", is permanent fixture in pg_class? What is your advice on this?

I'd be quite interested in this as well. Can one depend on this column
in the future? Even if not, as long as one verifies it still exists, is
this a viable option for trigger control within a transaction?

(This guy Tucker comes up with some interesting stuff...) :)

It is the only known way to control triggers though it isn't regularly
tested by the developers.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Geoffrey wrote:

Terry Lee Tucker wrote:

Tom,

Do you feel this is a safe method for disabling triggers in the rare cases
where one finds that it is prudent to do that? Do you think that the column,
"reltriggers", is permanent fixture in pg_class? What is your advice on this?

I'd be quite interested in this as well. Can one depend on this column
in the future? Even if not, as long as one verifies it still exists, is
this a viable option for trigger control within a transaction?

(This guy Tucker comes up with some interesting stuff...) :)

It is the only known way to control triggers though it isn't regularly
tested by the developers.

There's another way, provided you're willing to modify your triggers.
If so, you can gain per-session control over any and all triggers and
functions.

For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine.

You even get transaction support, so if you disable logging, then
rollback, logging will be turned back on in your next transaction.

Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (some_error) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;

The catch is, my logging trigger must be changed to look like this:

BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;

It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.

---------------------------------------
CREATE TABLE session_vars (
id INT PRIMARY KEY,
value BOOL NOT NULL,
description CHAR(20)
);

---------------------------------------
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');

---------------------------------------
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;

---------------------------------------
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;

---------------------------------------
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

It is the only known way to control triggers though it isn't regularly
tested by the developers.

I think I've come up with another way.. I posted this recently, but did
not get any feedback on it so I'm not sure how dumb it is... It is
working really great for me though....

All I did was added an extra column to my table (I called it
"batch_process"). Then in
the trigger do something like (in whichever function you are calling):

IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN
NEW.batch_process := NULL;
RETURN NULL;
END IF;
.. whatever the rest of transaction is

Then when doing an insert, just:

INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the
trigger not to fire...

Or an update:

UPDATE TABLE table SET ...., batch_process = 't' ....

I'm not sure sure how to make it work on a function called from a delete
trigger though.. ;-(

- Greg

Net Virtual Mailing Lists wrote:

All I did was added an extra column to my table (I called it
"batch_process"). Then in
the trigger do something like (in whichever function you are calling):

IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN
NEW.batch_process := NULL;
RETURN NULL;
END IF;
.. whatever the rest of transaction is

Why don't you just set it to false instead of NULL? Wouldn't that reduce
the condition to just "IF NEW.batch_update THEN ..."? In that case you
should default the column to false of course, or the condition will
always fail (the value being NULL).
Personally, I would use a more descriptive name for the column,
'disable_triggers' or something like that.

Also, I find it more convenient to use "true" and "false" instead of
having to escape "'t'" and "'f'" all the time ;)

Then when doing an insert, just:

INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the
trigger not to fire...

Or an update:

UPDATE TABLE table SET ...., batch_process = 't' ....

I'm not sure sure how to make it work on a function called from a delete
trigger though.. ;-(

The drawbacks of this method are that you'll have to modify all your
queries when you want to disable triggers (though that can usually be
solved programatically), and that only the triggers that "support" this
method of disabling will be actually disabled.

If you work at the same project with multiple people who all write
triggers from time to time, or when you have to deal with legacy code
from an older database, I think you'll run into trouble with the above
quicker than you'd like. However, if you manage to get this into the
design fase of a project it'll probably work just fine (the delete
problem you mentioned aside...).

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

Net Virtual Mailing Lists wrote:

All I did was added an extra column to my table (I called it
"batch_process"). Then in
the trigger do something like (in whichever function you are calling):

IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN
NEW.batch_process := NULL;
RETURN NULL;
END IF;
.. whatever the rest of transaction is

Why don't you just set it to false instead of NULL? Wouldn't that reduce
the condition to just "IF NEW.batch_update THEN ..."? In that case you
should default the column to false of course, or the condition will
always fail (the value being NULL).
Personally, I would use a more descriptive name for the column,
'disable_triggers' or something like that.

Yeah, that's one improvement I meant to make but just haven't gotten
around to it.. It is just the way this thing got written the first time
during and testing and the "oh! It worked!" realization.. ;-)

Also, I find it more convenient to use "true" and "false" instead of
having to escape "'t'" and "'f'" all the time ;)

Yeah.. ;-)

Then when doing an insert, just:

INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the
trigger not to fire...

Or an update:

UPDATE TABLE table SET ...., batch_process = 't' ....

I'm not sure sure how to make it work on a function called from a delete
trigger though.. ;-(

The drawbacks of this method are that you'll have to modify all your
queries when you want to disable triggers (though that can usually be
solved programatically), and that only the triggers that "support" this
method of disabling will be actually disabled.

It seems like you would have to do something programatically anyways in
order to say "Okay, now I want to disable the triggers -- go do something"....

If you work at the same project with multiple people who all write
triggers from time to time, or when you have to deal with legacy code
from an older database, I think you'll run into trouble with the above
quicker than you'd like. However, if you manage to get this into the
design fase of a project it'll probably work just fine (the delete
problem you mentioned aside...).

I think any of the solutions I've seen mentioned so far would present the
same problem..... :-(

[Here's my third attempt to post this. Sorry if it's a dup. (trip?)]

If you're willing to modify your triggers you can gain per-session
control over any and all triggers and functions.

For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine. I also want transaction
support, so if I disable logging, then rollback, logging will be turned
back on in my next transaction. Like this:

Usage Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (check_error()) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;

The catch is, my logging trigger must be changed to look like this:

BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;

It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.

---------------------------------------
CREATE TABLE session_vars (
id INT PRIMARY KEY,
value BOOL NOT NULL,
description CHAR(20)
);

---------------------------------------
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');

---------------------------------------
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;

---------------------------------------
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;

---------------------------------------
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION log_trigger() RETURNS trigger AS '
BEGIN
IF logging_enabled() THEN
--do_logging;
END IF;

RETURN NEW; --or something
' LANGUAGE plpgsql VOLATILE;

Hope that helps,

Adam Tomjack

I just got this rather wild idea, don't really have the opportunity to
think it through thoroughly right now...

Say, you create a table with a set of triggers that have some method of
checking whether triggers should be enabled in this session, and
containing some "stubs" where actual trigger implementations could be
inserted.

Wouldn't it work if you create all your tables inheriting that "special"
table? This way you would automagically have all your tables understand
the disabling of triggers in a session without having to worry about it.
Triggers on those tables would have to be activated through the stubs,
instead of by the usual means.

I am kind of curious where this could lead... :P

Adam Tomjack wrote:

If you're willing to modify your triggers you can gain per-session
control over any and all triggers and functions.

For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine. I also want transaction
support, so if I disable logging, then rollback, logging will be turned
back on in my next transaction. Like this:

Usage Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (check_error()) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;

The catch is, my logging trigger must be changed to look like this:

BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;

It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.

---------------------------------------
CREATE TABLE session_vars (
id INT PRIMARY KEY,
value BOOL NOT NULL,
description CHAR(20)
);

---------------------------------------
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');

---------------------------------------
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;

---------------------------------------
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;

---------------------------------------
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;

---------------------------------------
CREATE FUNCTION log_trigger() RETURNS trigger AS '
BEGIN
IF logging_enabled() THEN
--do_logging;
END IF;

RETURN NEW; --or something
' LANGUAGE plpgsql VOLATILE;

Hope that helps,

Adam Tomjack

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl