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.
Ideas, criticism?
Gavin