Thread: Triggers and System Tables

I have a brief question concerning triggers and system tables:
Why is it not allowed to trigger a system table? I guess it could be
useful from time to time.

    Hans

Hi,

--On Montag, 15. April 2002 21:55 +0200 Hans-Juergen Schoenig
<hs@cybertec.at> wrote:

> I have a brief question concerning triggers and system tables:
> Why is it not allowed to trigger a system table? I guess it could be
> useful from time to time.

I guess it could be usefull to get an endless loop of trigger pulling
from time to time ;))

Imagine a trigger on the table which postgres uses for holding the trigger
data. Creating the trigger means action on the table, which in turn
pulls the trigger ...

Regardless of this I agree that it would be usefull - but one would
have to be _very_ carefully with the design of the trigger.

Regards
Tino Wildenhain

Hans-Juergen Schoenig <hs@cybertec.at> writes:
> I have a brief question concerning triggers and system tables:
> Why is it not allowed to trigger a system table?

Most of the operations that modify system tables don't bother to fire
triggers ... so if you had a trigger, it would generally fail to capture
the interesting events.  I'm not sure what the feasibility would be of
firing triggers everywhere that system tables are updated.  Offhand I'd
be worried about whether the system is in a sufficiently self-consistent
state to support trigger execution at all those points.

It would probably also be a *really bad* idea to install a trigger that
tries to alter the row or suppress the update ;-)

            regards, tom lane

Hans-Juergen Schoenig wrote:
> I have a brief question concerning triggers and system tables:
> Why is it not allowed to trigger a system table? I guess it could be
> useful from time to time.

    Because  system  tables  are  often  modified  by direct heap
    access, rather than regular query plans handled  through  the
    executor. The direct heap access doesn't invoke the triggers.

    Even if we get this problem "fixed" (I don't really  consider
    it  broken),  what if you setup a ON UPDATE OR DELETE trigger
    for pg_proc, that is broken? You cannot remove or fix it  any
    more,  so  your  pg_proc  system  catalog is frozen, forever,
    irreversable, infinitely! That's bad!


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

My problem is that there is no simple way to find out when a table has
been created. I need something like a trigger on pg_class so that I can
check for new tables.
Is there a way to get around the problem?
I know that system tables are somehow special and that is not
recommended to implement some sort of "ordinary trigger" due to internal
reasons.
I wonder how I can find out that a table, a view or something like that
has been created if there is no way to start a function automatically.

Does anybody have an idea?

    Hans