Thread: Triggers on system tables
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
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
Gavin Sherry <swm@linuxworld.com.au> writes:
> There have been a few discussions about triggers on system tables in
> the past and the problems with such triggers seem to be:
I think the killer problem is that we couldn't allow triggers on system
tables to do very much. By definition, the database is in an inconsistent
state if we are in the middle of updating system tables. We can't allow
arbitrary bits of SQL to run then.
It might be feasible to allow AFTER triggers on system tables, since
those don't actually fire until end of statement, at which point things
are hopefully consistent again. I don't even want to think about
allowing BEFORE triggers.
regards, tom lane
On Wed, 11 Feb 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > There have been a few discussions about triggers on system tables in > > the past and the problems with such triggers seem to be: > > I think the killer problem is that we couldn't allow triggers on system > tables to do very much. By definition, the database is in an inconsistent > state if we are in the middle of updating system tables. We can't allow > arbitrary bits of SQL to run then. > > It might be feasible to allow AFTER triggers on system tables, since > those don't actually fire until end of statement, at which point things > are hopefully consistent again. I don't even want to think about > allowing BEFORE triggers. Actually, all my tests (CREATE TABLE case) have been for AFTER triggers as I didn't want to look at BEFORE cases either. If the trigger is executed after all system catalog/index updates, plus CommandCounterIncrement(), we should be consistent, right? My idea is to provide a generic interface which is called inside ProcessUtility() after all other functions are called for the particular node we're handling. The nodetag itself will be passed to this generic function, the function will map nodetag to the relevant underlying system table, get the TriggerDesc for the relation and pass it and the relation data onto the trigger system. CREATE will call insert triggers, ALTER will call update triggers, DROP will call delete. If this sounds good, I think we can deal with AFTER ... STATEMENT level triggers without having to worry about constructing state. Thanks, Gavin
On Wed, 2004-02-11 at 22:30, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > There have been a few discussions about triggers on system tables in > > the past and the problems with such triggers seem to be: > > I think the killer problem is that we couldn't allow triggers on system > tables to do very much. By definition, the database is in an inconsistent > state if we are in the middle of updating system tables. We can't allow > arbitrary bits of SQL to run then. Yes, the internal calls from one command to another make this tricky -- but the return Tag is set very early for the statement. Could the Before trigger for system commands (CREATE, ALTER, etc.) not be kicked of in the same area as the tag is set?
Gavin Sherry <swm@linuxworld.com.au> writes:
> My idea is to provide a generic interface which is called inside
> ProcessUtility() after all other functions are called for the particular
> node we're handling. The nodetag itself will be passed to this generic
> function, the function will map nodetag to the relevant underlying system
> table, get the TriggerDesc for the relation and pass it and the relation
> data onto the trigger system. CREATE will call insert triggers, ALTER will
> call update triggers, DROP will call delete.
I didn't actually much care for the "abstract event" aspect of your
proposal. I think that that presupposes too much about what people
will want to do with such triggers. Plus you still have to define
what data will be supplied to the trigger. I think it's fine to make
these things be real triggers that get real row values. We just have
to pay attention to calling them at a safe time.
One "state problem" that needs to be thought about is that many of the
system functions are implemented in a way that involves multiple steps
of updates on a single row. (In contrast, an ordinary UPDATE command
can't change a row more than once.) The intermediate states of these
rows are probably good examples of the sort of inconsistent data we
don't want to expose --- not only because they are inconsistent, but
because the exact intermediate states are implementation details that
are highly likely to change across versions. Can we collapse such
events together so that the user triggers see only the initial and final
states, and not the intermediate states?
regards, tom lane
Rod Taylor <pg@rbt.ca> writes:
> Yes, the internal calls from one command to another make this tricky --
> but the return Tag is set very early for the statement. Could the Before
> trigger for system commands (CREATE, ALTER, etc.) not be kicked of in
> the same area as the tag is set?
At that point there wouldn't be a "new row" available to pass to the
trigger. I'm unsure what such a trigger could usefully do.
regards, tom lane
On Wed, 11 Feb 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > My idea is to provide a generic interface which is called inside > > ProcessUtility() after all other functions are called for the particular > > node we're handling. The nodetag itself will be passed to this generic > > function, the function will map nodetag to the relevant underlying system > > table, get the TriggerDesc for the relation and pass it and the relation > > data onto the trigger system. CREATE will call insert triggers, ALTER will > > call update triggers, DROP will call delete. > > I didn't actually much care for the "abstract event" aspect of your > proposal. I think that that presupposes too much about what people > will want to do with such triggers. Plus you still have to define > what data will be supplied to the trigger. I think it's fine to make > these things be real triggers that get real row values. We just have > to pay attention to calling them at a safe time. Okay then. > > One "state problem" that needs to be thought about is that many of the > system functions are implemented in a way that involves multiple steps > of updates on a single row. (In contrast, an ordinary UPDATE command Yeah. That's what I liked about restricting it to CREATE TABLE, etc. We know when the state is consistent. > can't change a row more than once.) The intermediate states of these > rows are probably good examples of the sort of inconsistent data we > don't want to expose --- not only because they are inconsistent, but > because the exact intermediate states are implementation details that > are highly likely to change across versions. Can we collapse such > events together so that the user triggers see only the initial and final > states, and not the intermediate states? Do you have an example at hand of a system function which will face this problem so that I can see what is involved? Thanks, Gavin
Gavin Sherry <swm@linuxworld.com.au> writes:
> Do you have an example at hand of a system function which will face this
> problem so that I can see what is involved?
Mmm ... try CREATE TABLE with foreign keys. IIRC the basic table is
created and then we do ALTER TABLE ADD FOREIGN KEY.
regards, tom lane
On Thu, 12 Feb 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > Do you have an example at hand of a system function which will face this > > problem so that I can see what is involved? > > Mmm ... try CREATE TABLE with foreign keys. IIRC the basic table is > created and then we do ALTER TABLE ADD FOREIGN KEY. Ahh, of course. I misunderstood you. I was taking system 'function' literally. CREATE TABLE by itself is bad enough. We do ALTER TABLE ADD TOAST outside of DefineRelation(). Gavin
Added to TODO: * Allow AFTER triggers on system tables --------------------------------------------------------------------------- Gavin Sherry wrote: > On Thu, 12 Feb 2004, Tom Lane wrote: > > > Gavin Sherry <swm@linuxworld.com.au> writes: > > > Do you have an example at hand of a system function which will face this > > > problem so that I can see what is involved? > > > > Mmm ... try CREATE TABLE with foreign keys. IIRC the basic table is > > created and then we do ALTER TABLE ADD FOREIGN KEY. > > Ahh, of course. I misunderstood you. I was taking system 'function' > literally. CREATE TABLE by itself is bad enough. We do ALTER TABLE ADD > TOAST outside of DefineRelation(). > > Gavin > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073