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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
