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

[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