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

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