Re: Disabling triggers in a transaction - Mailing list pgsql-general

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;