Re: Disabling triggers in a transaction

Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> · Thu, 10 Mar 2005 10:50:59 +0100

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@xxxxxxxxxxxxxxxxx
W: http://www.magproductions.nl

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx