Re: Triggers on system tables

On Thu, 12 Feb 2004, Gavin Sherry wrote:

> Hi all,
>
> I've been thinking about triggers on system tables. The motivation for
> this is to allow for better interaction between the system and interfaces.
> For example, interfaces could store database meta data (list of tables)
> and have the cache invalidated by a trigger on pg_class (calling a
> function which NOTIFYs an event, etc).
>
> There have been a few discussions about triggers on system tables in
> the past and the problems with such triggers seem to be:
>
> 1) system table modificications do not (should not) go through the
> executor, where the existing trigger code works out of.
>
> 2) If we're not in the executor, there might be some state based problems
> which mean that triggers cannot be run.
>
> 3) We can't (shouldn't?) hand system table data off to user land trigger
> functions for manipulation
>
> I've looked into these three problems. To solve (1) means we just have to
> interface to the trigger system within low-level DDL routines. I've rigged
> up a test trigger on pg_class and fired it from within
> heap_create_with_catalog() and came across no problems. That's not that
> say that problems don't exist, but I think any state problems can be
> solved.
>
> I think (3) is important. I think we can solve a host of problems by
> looking at triggers on system tables differently. Neil has suggested
> adding triggers on DDL commands as opposed to system tables. For example:
>
> CREATE TRIGGER foo AFTER CREATE TABLE FOR EACH STATEMENT ...
>
> Now, because a DDL command may touch several system tables, the trigger is
> not (from a user point of view) on a table as such (in reality, the
> trigger would be on pg_class for this example). For this reason, we only
> allow statement level triggers. This resolves the conceptual problem of
> passing catalog data onto user functions as well as some potential state
> problems, I think.
>
> There'll be a few other issues. The first one that comes to mind is system
> table cache invalidation inside the create/drop trigger routines if a
> trigger is (effectively) being created on a system table.

I meant to add, doing it this way will give us the flexibility to avoiding
having to support system table triggers on every system table. This way,
we can just support { CREATE|ALTER|DROP } TABLE and, say, { CREATE | DROP
} TYPE if other DDLs present significant issues.

It is, of course, reasonable to want the feature supported on all DDL
commands.

>
> Ideas, criticism?
>

Gavin